C# 操作excel多个sheet

最新推荐文章于 2022-11-25 16:12:09 发布

原创最新推荐文章于 2022-11-25 16:12:09 发布 · 2.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#excel #c# #dataset #null #string #exception

本文介绍了一种使用C#将DataSet中的多个DataTable分别导出到不同工作表的方法。重点讲解了如何创建新的Excel文件、为每个DataTable创建相应的工作表并正确命名、设置单元格格式及数据填充等步骤。

private void DataViewExcelBySheetMultipleDt(DataSet ds, string fileName) { try { int sheetCount = ds.Tables.Count; GC.Collect(); Application excel; _Workbook xBk; _Worksheet xSt = null; excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); int rowIndex = 0; int colIndex = 0; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { rowIndex = 1; colIndex = 1; xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); switch (sheetIndex) { case 0: xSt.Name = "test1"; break; case 1: xSt.Name = "test2"; break; case 2: xSt.Name = "test3"; break; case 3: xSt.Name = "test4"; break; } foreach (DataColumn col in ds.Tables[sheetIndex].Columns) { xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true; excel.Cells[rowIndex, colIndex++] = col.ColumnName; } foreach (DataRow row in ds.Tables[sheetIndex].Rows) { rowIndex++; colIndex = 1; foreach (DataColumn col in ds.Tables[sheetIndex].Columns) { if (col.DataType == System.Type.GetType("System.DateTime")) { if (!"".Equals(row[col.ColumnName].ToString())) excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("MM/dd/yyyy"); else excel.Cells[rowIndex, colIndex] = ""; } else if (col.DataType == System.Type.GetType("System.String")) { excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); } else { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } colIndex++; } } Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]); allDataWithTitleRange.Select(); allDataWithTitleRange.Columns.AutoFit(); allDataWithTitleRange.Borders.LineStyle = 1; } string exportDir = "~/Attachment/"; string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(exportDir, fileName)); xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } catch (Exception ex) { } }

上面方法,首先形成一个多个DataTable的DataSet,
重点还是,1. 生成一个新的xls时,打开方式,总是会提示进程占用,就像楼上说的那样
2. 用不同的sheet时一定要命名
3. 使用传入一个datatable时,总是会重写第一个sheet