C#导出数据到Excel文件的方法
作者:gogo 时间:2023-11-13 17:27:05
本文实例讲述了C#导出数据到Excel文件的方法。分享给大家供大家参考。具体实现方法如下:
/// <summary>
/// 导出到Excel类,项目需引用Microsodt.Office.Interop.Excel,
/// 类文件需using System.Data与System.Windows.Forms命名空间
/// </summary>
public class CToExcel
{
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="fileName">默认文件名</param>
/// <param name="listView">数据源,一个页面上的ListView控件</param>
/// <param name="titleRowCount">标题占据的行数,为0表示无标题</param>
public void ExportExcel(string fileName, System.Windows.Forms.ListView listView,int titleRowCount)
{
string saveFileName = "";
//bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
}
catch (Exception)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
}
finally
{
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写Title
if(titleRowCount!=0)
MergeCells(worksheet, 1, 1, titleRowCount, listView.Columns.Count, listView.Tag.ToString());
//写入列标题
for (int i = 0; i <= listView.Columns.Count - 1; i++)
{
worksheet.Cells[titleRowCount+1, i + 1] = listView.Columns[i].Text;
}
//写入数值
for (int r = 0; r <= listView.Items.Count - 1; r++)
{
for (int i = 0; i <= listView.Columns.Count - 1; i++)
{
worksheet.Cells[r + titleRowCount+2, i + 1] = listView.Items[r].SubItems[i].Text;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
//{
// Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
// rg.NumberFormat = "00000000";
//}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开!n" + ex.Message);
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
MessageBox.Show(fileName + "导出到Excel成功", "提示", MessageBoxButtons.OK);
}
/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="fileName">默认的文件名</param>
/// <param name="dataTable">数据源,一个DataTable数据表</param>
/// <param name="titleRowCount">标题占据的行数,为0则表示无标题</param>
public void ExportExcel(string fileName,System.Data.DataTable dataTable,int titleRowCount)
{
string saveFileName = "";
//bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
}
catch (Exception)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
}
finally
{
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写Title
if(titleRowCount!=0)
MergeCells(worksheet, 1, 1, titleRowCount, dataTable.Columns.Count, dataTable.TableName);
//写入列标题
for (int i = 0; i <= dataTable.Columns.Count - 1; i++)
{
worksheet.Cells[titleRowCount+1, i + 1] = dataTable.Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r <= dataTable.Rows.Count - 1; r++)
{
for (int i = 0; i <= dataTable.Columns.Count - 1; i++)
{
worksheet.Cells[r +titleRowCount+ 2, i + 1] = dataTable.Rows[r][i].ToString();
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
//{
// Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
// rg.NumberFormat = "00000000";
//}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开!n" + ex.Message);
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
MessageBox.Show(fileName + "导出到Excel成功", "提示", MessageBoxButtons.OK);
}
/// <summary>
/// 合并单元格,并赋值,对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
Microsoft.Office.Interop.Excel.Range range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除,合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
希望本文所述对大家的C#程序设计有所帮助。
标签:C#,导出,Excel
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Android自定义view仿淘宝快递物流信息时间轴
2022-02-20 18:51:16
![](https://img.aspxhome.com/file/2023/7/139387_0s.jpg)
C#实现字符串与图片的Base64编码转换操作示例
2021-07-06 14:29:21
C#微信公众号开发之服务器配置
2023-03-12 15:02:50
![](https://img.aspxhome.com/file/2023/0/100170_0s.png)
C#实现对Json字符串处理实例
2023-06-21 08:26:24
Android studio实现画板功能
2022-08-04 21:30:39
![](https://img.aspxhome.com/file/2023/2/137172_0s.png)
深入浅析C#泛型类型
2023-01-30 06:45:54
![](https://img.aspxhome.com/file/2023/9/111139_0s.png)
详解Spring Data JPA使用@Query注解(Using @Query)
2023-11-29 14:49:34
详解java 中Spring jsonp 跨域请求的实例
2023-11-19 02:48:18
![](https://img.aspxhome.com/file/2023/7/59667_0s.png)
全面解析Android之ANR日志
2023-12-18 23:30:21
![](https://img.aspxhome.com/file/2023/3/138103_0s.png)
sin(x)如何求解的java代码实现方法
2022-04-17 16:22:07
![](https://img.aspxhome.com/file/2023/3/64793_0s.jpg)
Android中Root权限获取的简单代码
2021-10-22 20:42:52
C#中使用Join与GroupJoin将两个集合进行关联与分组
2023-11-11 23:40:47
![](https://img.aspxhome.com/file/2023/2/119522_0s.jpg)
Android RadioButton单选框的使用方法
2021-10-02 14:37:20
java实现按层遍历二叉树
2021-12-04 06:58:35
springboottest测试依赖和使用方式
2021-11-21 13:41:38
![](https://img.aspxhome.com/file/2023/7/74757_0s.png)
Android入门之实现手工发送一个BroadCast
2023-08-10 13:54:16
![](https://img.aspxhome.com/file/2023/9/139139_0s.jpg)
对指定的网页进行截图的效果 C#版
2022-07-04 03:14:18
Mybatis Plus select 实现只查询部分字段
2022-07-23 18:51:32
![](https://img.aspxhome.com/file/2023/9/61279_0s.jpg)
C# Winform 调用系统接口操作 INI 配置文件的代码
2023-03-04 11:49:54
Java HtmlEmail 邮件发送的简单实现代码
2023-04-14 21:29:25
![](https://img.aspxhome.com/file/2023/8/111448_0s.png)