C# Excel导出数据
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.IO; using System.Data.OleDb; using Excel = Microsoft.Office.Interop.Excel; using System.Reflection; using System.Collections; namespace ConSystemComponent.DataAccess { public class ExcelOperator { /// <summary> ///将DataTable数据导出到Excel文件 /// </summary> /// <remarks> /// add com "Microsoft Excel 11.0 Object Library" /// using Excel=Microsoft.Office.Interop.Excel; /// using System.Reflection; /// </remarks> /// <param name="dt">将要导出的DataTable</param> /// <param name="savePath">生成Excel文件的保存路径(末尾不要加\\)</param> /// <returns>返回生成Excel文件的完整路径</returns> public string DataTableToExcel(DataTable dt, string savePath) { DateTime now = DateTime.Now; string FileName = now.Year.ToString().PadLeft(2) + now.Month.ToString().PadLeft(2, '0') + now.Day.ToString().PadLeft(2, '0') + "-" + now.Hour.ToString().PadLeft(2, '0') + now.Minute.ToString().PadLeft(2, '0') + now.Second.ToString().PadLeft(2, '0'); //验证strFileName是否为空或值无效 if (FileName.Trim() == "") { return "保存路径不正确!"; } //if (!Directory.Exists(savePath)) //{ // return "保存路径不正确!"; //} FileName = FileName + ".xls"; //定义表格内数据的行数和列数 int rowscount = dt.Rows.Count; int colscount = dt.Columns.Count; //行数必须大于0 if (rowscount <= 0) { return "没有数据可供保存!"; } //列数必须大于0 if (colscount <= 0) { return "没有数据可供保存!"; } //行数不可以大于65536 if (rowscount > 65536) { return "数据记录数太多(最多不能超过65536条),不能保存!"; } //列数不可以大于255 if (colscount > 255) { return "数据记录行数太多,不能保存 "; } //验证以fileName命名的文件是否存在,如果存在删除它 FileInfo file = new FileInfo(FileName); if (file.Exists) { try { file.Delete(); } catch { return "试图删除现有文件" + FileName + "失败!"; } } Excel.Application objExcel = null; Excel.Workbook objWorkbook = null; Excel.Worksheet objsheet = null; try { //申明对象 objExcel = new Microsoft.Office.Interop.Excel.Application(); objWorkbook = objExcel.Workbooks.Add(Missing.Value); objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet; //设置EXCEL不可见 objExcel.Visible = false; //向Excel中写入表格的表头 int displayColumnsCount = 1; for (int i = 0; i <= dt.Columns.Count - 1; i++) { //设置单元格格式 objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[1, displayColumnsCount]).Font.Bold = true; objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[1, displayColumnsCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objExcel.Cells[1, displayColumnsCount] = Arr[i]; displayColumnsCount++; } //向Excel中逐行逐列写入表格中的数据 for (int row = 0; row <= dt.Rows.Count - 1; row++) { displayColumnsCount = 1; for (int col = 0; col < colscount; col++) { objExcel.Cells[row + 2, displayColumnsCount] = dt.Rows[row][col].ToString().Trim(); if (dt.Columns[col].ColumnName == "Attribute11" || dt.Columns[col].ColumnName == "Attribute12") { if (dt.Rows[row][col].ToString().Trim() == "1") { objExcel.Cells[row + 2, displayColumnsCount] = "发送"; } else { objExcel.Cells[row + 2, displayColumnsCount] = "不发送"; } } displayColumnsCount++; } } //设置单元格格式 Excel.Range excelRange = objsheet.get_Range(objsheet.Cells[1, 1], objsheet.Cells[rowscount+1, colscount]); excelRange.Borders.LineStyle = 1; //保存文件 //objWorkbook.SaveAs(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, // Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, // Missing.Value, Missing.Value); objExcel.Save(FileName); } catch (Exception error) { return "生成Excel文件出错:" + error.Message; } finally { //关闭Excel应用 if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value); if (objExcel.Workbooks != null) objExcel.Workbooks.Close(); if (objExcel != null) objExcel.Quit(); objsheet = null; objWorkbook = null; objExcel = null; } return FileName; } /// <summary> /// 获取表头信息 /// </summary> public ArrayList Arr { get { ArrayList arr=new ArrayList(); arr.Add("公司"); arr.Add("name"); arr.Add("age"); arr.Add("地址"); arr.Add("公司电话"); arr.Add("手机号码"); arr.Add("邮箱地址A"); arr.Add("邮箱地址B"); arr.Add("状态1"); arr.Add("状态2"); arr.Add("编号"); return arr; } } } }