C#导出Excel报表
来源:互联网 发布:安卓软件模拟器 编辑:程序博客网 时间:2024/04/28 01:29
在使用前首先需要添加引用:Microsoft.Office.Interop.Excel,然后在调用页添加如下语句:
using Excel = Microsoft.Office.Interop.Excel;
/**//// <summary>
/// 导出为Excel格式文件
/// </summary>
/// <param name="dt">作为数据源的DataTable</param>
/// <param name="saveFile">带路径的保存文件名</param>
/// <param name="title">一个Excel sheet的标题</param>
private void exportExcel(DataTable dt,string saveFile,string title)
...{
rptExcel = new Microsoft.Office.Interop.Excel.Application();
if (rptExcel == null)
...{
MessageBox.Show("无法打开EXcel,请检查Excel是否可用或者是否安装好Excel", "系统提示");
return;
}
int rowCount = dt.Rows.Count;//行数
int columnCount = dt.Columns.Count;//列数
float percent = 0;//导出进度
this.Cursor = Cursors.WaitCursor;
//保存文化环境
System.Globalization.CultureInfo currentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbook workbook = rptExcel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
worksheet.Name = "报表";//一个sheet的名称
//rptExcel.Visible = true;//打开导出的Excel文件
worksheet.Cells[1, 1] = title;//表标题
//填充列标题
for (int i = 0; i < columnCount; i++)
...{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
}
//创建对象数组存储DataTable的数据,这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
object[,] objData = new object[rowCount, columnCount];
//填充内容到对象数组
for (int r = 0; r < rowCount; r++)
...{
for (int col = 0; col < columnCount; col++)
...{
objData[r, col] = dt.Rows[r][col].ToString();
}
percent = ((float)(r+1) * 100) / rowCount;
this.panelProgress.Visible = true;//显示进度条
this.lblPercents.Text=percent.ToString("n") + "%";
this.progressBar1.Value = Convert.ToInt32(percent);
System.Windows.Forms.Application.DoEvents();
}
//将对象数组的值赋给Excel对象
Excel.Range range = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowCount + 2, columnCount]);
range.NumberFormat = "@";//设置数字文本格式
range.Value2 = objData;
//设置格式
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).MergeCells = true;//合并单元格
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//居中对齐
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).RowHeight = 38;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Bold = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Name = "黑体";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Size = 16;
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 2, columnCount]).Borders.LineStyle = 1;//设置边框
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount, columnCount]).Columns.AutoFit();//设置单元格宽度为自适应
//恢复文化环境
System.Threading.Thread.CurrentThread.CurrentCulture = currentCI;
try
...{
//rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里,如果不用SaveFileDialog就可用这种方法
workbook.Saved=true;
workbook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
this.Cursor = Cursors.Default;
this.panelProgress.Visible = false;//隐藏进度条
MessageBox.Show("恭喜,数据已经成功导出为Excel文件!", "成功导出");
}
catch (Exception ex)
...{
MessageBox.Show("导出文件出错,文件可能正被打开,具体原因:" + ex.Message, "出错信息");
}
finally
...{
dt.Dispose();
rptExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(rptExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
GC.Collect();
KillAllExcel();
}
}
/**//// <summary>
/// 获得所有的Excel进程
/// </summary>
/// <returns>所有的Excel进程</returns>
private List<Process> GetExcelProcesses()
...{
Process[] processes = Process.GetProcesses();
List<Process> excelProcesses = new List<Process>();
for (int i = 0; i < processes.Length; i++)
...{
if (processes[i].ProcessName.ToUpper() == "EXCEL")
excelProcesses.Add(processes[i]);
}
return excelProcesses;
}
private void KillAllExcel()
...{
List<Process> excelProcess = GetExcelProcesses();
for (int i = 0; i < excelProcess.Count; i++)
...{
excelProcess[i].Kill();
}
}
- C#导出Excel报表
- C# 导出EXCel报表
- C#中导出Excel报表的方法
- winform导出Excel报表之C#操作EXCEL 1
- c# 导入excel处理数据,导出excel报表
- PHP 导出EXCEL报表
- 导出Word、Excel报表
- .net 导出Excel报表
- java 导出 Excel 报表
- 采用excel导出报表
- 导出excel报表
- 导出excel报表
- Excel报表的导出
- php导出excel报表
- Excel 报表导入导出
- poi报表导出excel
- Excel导出报表
- C#操作EXCEL导出数据报表的类
- HDU 4125 2011福州现场赛E题 KMP+笛卡尔树
- Eclipse中恢复已删除文件和还原到先前版本
- 新技术抢眼仍露怯 诺基亚能借新品翻身?
- Magento中通过访问Url把产品加入购物车
- Android 将TabHost放在最下方显示
- C#导出Excel报表
- 无法打开用户默认数据库,登录失败
- 求最大子序列和
- 相约世纪末
- 得到tinymce富文本编辑中的内容
- error LNK2005
- LaTex cite.sty not found
- 来确定对象是否是某一个类的成员
- Android编程之manifest上遇到的错误