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操作一直是程序难题,做的很好的三方插件基本都是要花钱的,所以。。。。
转载于:https://www.cnblogs.com/error500/articles/2632110.html