1、工具类(applibrary.dll)
public class ExcelHelper { /// <summary> /// 文件名 /// </summary> public string fileName { get; set; } /// <summary> /// 工作簿列表 /// </summary> public List<WorkSheet> workSheets { get; set; } /// <summary> /// Excel文档对象 /// </summary> public XlsDocument doc { get; set; } public string message { get; set; } public ExcelHelper() { doc = new XlsDocument(); workSheets = new List<WorkSheet>(); } /// <summary> /// 导出Excel /// </summary> /// <returns></returns> public bool ToExcel() { if (string.IsNullOrEmpty(fileName)) { message = "请输入文件名"; return false; } if (workSheets.Count == 0) { message = "请指定工作簿数据"; return false; } doc.FileName = fileName; foreach (var workSheet in workSheets) { AppLibrary.WriteExcel.Worksheet sheet = null; //判断Worksheet是否存在 try { sheet = doc.Workbook.Worksheets[workSheet.name]; } catch (Exception ex) { sheet = doc.Workbook.Worksheets.Add(workSheet.name); } AppLibrary.WriteExcel.Cells cells = sheet.Cells; var dt = workSheet.table; for (int j = 0; j <= dt.Columns.Count - 1; j++) { if (workSheet.xf != null) { cells.Add(workSheet.startX, workSheet.startY + j, dt.Columns[j].ColumnName, workSheet.xf); } else { cells.Add(workSheet.startX, workSheet.startY + j, dt.Columns[j].ColumnName); } } for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { cells.Add(i + 1 + workSheet.startX, j + workSheet.startY, dt.Rows[i][j].ToEString()); } } } doc.Send(); return true; } } public class WorkSheet { private int _startX = 1; private int _startY = 1; public string name { get; set; } public DataTable table { get; set; } public int startX { get { return _startX; } set { if (value > 1) { _startX = value; } } } public int startY { get { return _startY; } set { if (value > 1) { _startY = value; } } } public AppLibrary.WriteExcel.XF xf { get; set; } }
2、调用代码:
ExcelHelper excel = new ExcelHelper(); excel.fileName = batchNo + ".xls"; //汇总 sql = string.Format(@"SELECT * from SKU WHERE batchNo='{0}'", batchNo); ret = dbHelper.Query(sql); AppLibrary.WriteExcel.XF xfcolumn = excel.doc.NewXF(); xfcolumn.Font.Bold = true; xfcolumn.Pattern = 1; xfcolumn.PatternColor = AppLibrary.WriteExcel.Colors.Cyan; excel.workSheets.Add(new WorkSheet() { name = "自动汇总", table = ret.Tables[0], xf = xfcolumn });
3、设置样式和宽度,表头和单元格样式要分开设置,申明两个XF变量。
#region 设置样式
//设置单元格样式 var xf = doc.NewXF(); xf.UseBorder = true; xf.LeftLineStyle = 1; xf.RightLineStyle = 1; xf.TopLineStyle = 1; xf.BottomLineStyle = 1; xf.LeftLineColor = AppLibrary.WriteExcel.Colors.Black; xf.RightLineColor = AppLibrary.WriteExcel.Colors.Black; xf.TopLineColor = AppLibrary.WriteExcel.Colors.Black; xf.BottomLineColor = AppLibrary.WriteExcel.Colors.Black; xf.UseFont = true; //设置表头样式 var thXf = doc.NewXF(); thXf.Pattern = 1; thXf.PatternColor = AppLibrary.WriteExcel.Colors.Default1B; thXf.UseBorder = true; thXf.LeftLineStyle = 1; thXf.RightLineStyle = 1; thXf.TopLineStyle = 1; thXf.BottomLineStyle = 1; thXf.LeftLineColor = AppLibrary.WriteExcel.Colors.Black; thXf.RightLineColor = AppLibrary.WriteExcel.Colors.Black; thXf.TopLineColor = AppLibrary.WriteExcel.Colors.Black; thXf.BottomLineColor = AppLibrary.WriteExcel.Colors.Black; thXf.Font.Bold = true; #endregion #region 宽度 AddColumnInfo(doc, sheet, 0, 8, 15); AddColumnInfo(doc, sheet, 9, 9, 30); AddColumnInfo(doc, sheet, 10, 15, 15); #endregion
private static void AddColumnInfo(AppLibrary.WriteExcel.XlsDocument doc, AppLibrary.WriteExcel.Worksheet sheet, int start, int end, int width) { AppLibrary.WriteExcel.ColumnInfo colInfo = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet); colInfo.ColumnIndexStart = (ushort)start; colInfo.ColumnIndexEnd = (ushort)end; colInfo.Width = (ushort)(width * 256); sheet.AddColumnInfo(colInfo); }
说明:
1、一个Sheet表中可以存放多张DataTable数据
2、服务端无需安装Excel
3、Send方法直接导出到浏览器端,无需先存本地再读取本地文件转换成byte输出