C#导出Excel的两种常见方式
一、MS COM组建创建Excel并保存
这种方式服务器需要安装office,版本不受限制
代码如下:
View Code
1 //将数据填充到Excel并保存 2 static void createExcel(List list) 3 { 4 if (list.Count == 0) 5 { 6 return; 7 } 8 var x1 = new Excel.Application(); 9 x1.Workbooks.Add(); 10 x1.Range["A1"].Value2 = "序号"; 11 x1.Range["B1"].Value2 = "SKU"; 12 x1.Range["C1"].Value2 = "内部款号"; 13 x1.Range["D1"].Value2 = "商品名"; 14 x1.Range["E1"].Value2 = "花色"; 15 x1.Range["F1"].Value2 = "尺码"; 16 x1.Range["G1"].Value2 = "当前库存"; 17 18 //设置行高 19 x1.Range["A1"].RowHeight = 30; 20 //设置背景色 21 x1.Range["A1"].Interior.ColorIndex = 6; 22 x1.Range["B1"].Interior.ColorIndex = 6; 23 x1.Range["C1"].Interior.ColorIndex = 6; 24 x1.Range["D1"].Interior.ColorIndex = 6; 25 x1.Range["E1"].Interior.ColorIndex = 6; 26 x1.Range["F1"].Interior.ColorIndex = 6; 27 x1.Range["G1"].Interior.ColorIndex = 6; 28 29 x1.Range["A2"].Select();//设置焦点 30 31 int i = 0; 32 foreach (var item in list) 33 { 34 x1.ActiveCell.Offset[i, 0].Value2 = item.Index; 35 x1.ActiveCell.Offset[i, 1].Value2 = item.SKU; 36 x1.ActiveCell.Offset[i, 2].Value2 = item.InternalCode; 37 x1.ActiveCell.Offset[i, 3].Value2 = item.GoodName; 38 x1.ActiveCell.Offset[i, 4].Value2 = item.ColorValue; 39 x1.ActiveCell.Offset[i, 5].Value2 = item.SizeValue; 40 x1.ActiveCell.Offset[i, 6].Value2 = item.CanOrderCount; 41 42 //如果库存数小于0,给出红色标记 43 if (item.CanOrderCount < 0) 44 { 45 x1.ActiveCell.Offset[i, 0].Interior.Color = 255; 46 x1.ActiveCell.Offset[i, 1].Interior.Color = 255; 47 x1.ActiveCell.Offset[i, 2].Interior.Color = 255; 48 x1.ActiveCell.Offset[i, 3].Interior.Color = 255; 49 x1.ActiveCell.Offset[i, 4].Interior.Color = 255; 50 x1.ActiveCell.Offset[i, 5].Interior.Color = 255; 51 x1.ActiveCell.Offset[i, 6].Interior.Color = 255; 52 } 53 i++; 54 } 55 56 //自适应单元格宽度和高度 57 ((Excel.Range)x1.Columns[1]).AutoFit(); 58 ((Excel.Range)x1.Columns[2]).AutoFit(); 59 ((Excel.Range)x1.Columns[3]).AutoFit(); 60 ((Excel.Range)x1.Columns[4]).AutoFit(); 61 ((Excel.Range)x1.Columns[5]).AutoFit(); 62 ((Excel.Range)x1.Columns[6]).AutoFit(); 63 ((Excel.Range)x1.Columns[7]).AutoFit(); 64 65 x1.ActiveCell.Offset[i, 0].Select();//设置焦点 66 x1.DisplayAlerts = false; 67 //保存Excel 68 x1.ActiveWorkbook.SaveAs(@"myExcel.xlsx"); 69 70 x1.Quit(); 71 GC.Collect(); 72 }
二、NPOI创建Excel并保存
这种方式需要引用NPOI动态链接库,并且只持支office2003 服务器不需要安装office
代码如下:
View Code
1 static void RenderDataToExcel(IEnumerable list) 2 { 3 //创建对象 4 HSSFWorkbook workbook = new HSSFWorkbook(); 5 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); 6 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); 7 8 var cellFont = workbook.CreateFont(); 9 cellFont.Color = NPOI.HSSF.Util.HSSFColor.RED.index; 10 HSSFCellStyle cellstyle = (HSSFCellStyle)workbook.CreateCellStyle(); 11 cellstyle.SetFont(cellFont); 12 13 //headerRow.Height = 30; 14 // 设置表头名称 15 headerRow.CreateCell(0).SetCellValue("序号"); 16 headerRow.CreateCell(1).SetCellValue("SKU"); 17 headerRow.CreateCell(2).SetCellValue("内部款号"); 18 headerRow.CreateCell(3).SetCellValue("商品名"); 19 headerRow.CreateCell(4).SetCellValue("花色"); 20 headerRow.CreateCell(5).SetCellValue("尺码"); 21 headerRow.CreateCell(6).SetCellValue("当前库存"); 22 //自适应宽度 23 sheet.AutoSizeColumn(0); 24 //写入数据 25 int rowIndex = 1; 26 foreach (var item in list) 27 { 28 //单元格数据填充 29 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); 30 dataRow.CreateCell(0).SetCellValue(item.Index); 31 dataRow.CreateCell(1).SetCellValue(item.SKU); 32 dataRow.CreateCell(2).SetCellValue(item.InternalCode); 33 dataRow.CreateCell(3).SetCellValue(item.GoodName); 34 dataRow.CreateCell(4).SetCellValue(item.ColorValue); 35 dataRow.CreateCell(5).SetCellValue(item.SizeValue); 36 dataRow.CreateCell(6).SetCellValue(item.CanOrderCount); 37 38 //设置单元格数据类型为字符串 39 dataRow.Cells[0].SetCellType(CellType.STRING); 40 dataRow.Cells[1].SetCellType(CellType.STRING); 41 dataRow.Cells[2].SetCellType(CellType.STRING); 42 dataRow.Cells[3].SetCellType(CellType.STRING); 43 dataRow.Cells[4].SetCellType(CellType.STRING); 44 dataRow.Cells[5].SetCellType(CellType.STRING); 45 dataRow.Cells[6].SetCellType(CellType.STRING); 46 47 //库存小于0,醒目标记 48 if (item.CanOrderCount < 0) 49 { 50 dataRow.Cells[0].CellStyle = cellstyle; 51 dataRow.Cells[1].CellStyle = cellstyle; 52 dataRow.Cells[2].CellStyle = cellstyle; 53 dataRow.Cells[3].CellStyle = cellstyle; 54 dataRow.Cells[4].CellStyle = cellstyle; 55 dataRow.Cells[5].CellStyle = cellstyle; 56 dataRow.Cells[6].CellStyle = cellstyle; 57 } 58 59 //自适应宽度 60 sheet.AutoSizeColumn(rowIndex); 61 rowIndex++; 62 } 63 //保存 64 string path = @"myExcel.xls"; 65 FileStream file = new FileStream(path, FileMode.Create); 66 workbook.Write(file); 67 //关闭文件,释放对象 68 file.Close(); 69 sheet = null; 70 headerRow = null; 71 workbook = null; 72 }
总结:
以上两种办法只能满足基本需求,对于复杂的处理无能为力,其实Excel操作一直是程序难题,做的很好的三方插件基本都是要花钱的,所以。。。。