NPOI根据模板导出Excel

上篇讲到使用Office Com组件的方式导出Excel,本篇讲一种个人认为更普遍的方式：NPOI,它无需服务器上安装Office，如果玩NPOI比较熟的话，个人更推荐使用这种方式。

本篇将介绍基本的导出Excel的方法，和合并单元格，设置单元格样式和如何插入图片等。

1.添加对NPOI.dll的引用，引入命名空间：

1 using NPOI;
2 using NPOI.HPSF;
3 using NPOI.HSSF.UserModel;
4 using NPOI.POIFS.FileSystem;
5 using NPOI.SS.Util;
6 using NPOI.SS.UserModel;

其余部分,直接上代码：

 1  protected void btn_Click(object sender, EventArgs e)
 2         {
 3 
 4             #region 创建DataTable并填充数据
 5             DataTable dt = new DataTable();
 6             dt.Columns.Add("name", System.Type.GetType("System.String"));
 7             dt.Columns.Add("age", System.Type.GetType("System.Int32"));
 8             DataRow newRow;
 9             newRow = dt.NewRow();
10             newRow["name"] = "zhangsan";
11             newRow["age"] = 30;
12             dt.Rows.Add(newRow);
13 
14             newRow = dt.NewRow();
15             newRow["name"] = "lisi";
16             newRow["age"] = 20;
17             dt.Rows.Add(newRow);
18             #endregion
19 
20             string TempletFileName = Server.MapPath("~/Template.xls");      //模板文件  
21             string ReportFileName = Server.MapPath("~/Restlt.xls");    //导出文件  
22             FileStream file = null;
23             try
24             {
25                 file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
26             }
27             catch (Exception)
28             {
29                 Response.Write("<script>alert('模板文件不存在或正在打开');</script>");
30                 return;
31             }
32             HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
33             HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
34             if (ws == null)//工作薄中没有工作表
35             {
36                 Response.Write("<script>alert('工作薄中没有Sheet1工作表');</script>");
37                 return;
38             }
39             int count = dt.Rows.Count;
40             if (count > 0)
41             {
42                 for (int i = 0; i < count; i++)
43                 {
44                     int _row = i; //i + 1;
45                     HSSFRow row = (HSSFRow)ws.CreateRow(_row);
46                     row.CreateCell(0).SetCellValue(dt.Rows[i]["name"].ToString().Trim());
47                     row.CreateCell(1).SetCellValue(dt.Rows[i]["age"].ToString().Trim());
48 
49                     ws.SetColumnWidth(0, 12 * 256);//设置列宽
50                     ws.SetColumnWidth(1, 12 * 256);//设置列宽
51                 }
52             }
53             ws.ForceFormulaRecalculation = true;
54 
55             using (FileStream filess = File.OpenWrite(ReportFileName))
56             {
57                 hssfworkbook.Write(filess);
58             }
59             System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);
60             Response.Clear();
61             Response.Charset = "GB2312";
62             Response.ContentEncoding = System.Text.Encoding.UTF8;
63             Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("result.xls"));
64             Response.AddHeader("Content-Length", filet.Length.ToString());
65             Response.ContentType = "application/ms-excel";
66             Response.WriteFile(filet.FullName);
67             Response.End();
68         }

下面附带一下NPOI样式的介绍：

NPOI设置样式比Office Com组件的方式要麻烦的多，比如Office Com组件有个Range类可以选取单元格的范围，NPOI死活找不到。

NPOI可以先设置一些样式，然后在后面使用：

 1             #region 各种样式
 2             //自定义的样式
 3             ICellStyle styleOne = hssfworkbook.CreateCellStyle();
 4             styleOne.BorderBottom = BorderStyle.Thick;
 5             styleOne.BorderLeft = BorderStyle.Thick;
 6             styleOne.BorderRight = BorderStyle.Thick;
 7             styleOne.BorderTop = BorderStyle.Thick;
 8             styleOne.VerticalAlignment = VerticalAlignment.Center;
 9             //styleOne.FillBackgroundColor = 244;          
10             styleOne.WrapText = true;
11             NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();
12             font.FontHeight = 16 * 16;
13             font.Boldweight = 700;
14             styleOne.SetFont(font);
15 
16             //垂直居中
17             ICellStyle style0 = hssfworkbook.CreateCellStyle();
18             style0.VerticalAlignment = VerticalAlignment.Center;
19 
20             //四周薄边框
21             ICellStyle style1 = hssfworkbook.CreateCellStyle();
22             style1.BorderBottom = BorderStyle.Thin;
23             style1.BorderLeft = BorderStyle.Thin;
24             style1.BorderRight = BorderStyle.Thin;
25             style1.BorderTop = BorderStyle.Thin;
26             style1.VerticalAlignment = VerticalAlignment.Center;
27 
28             //四周厚边框
29             ICellStyle style2 = hssfworkbook.CreateCellStyle();
30             style2.BorderBottom = BorderStyle.Thick;
31             style2.BorderLeft = BorderStyle.Thick;
32             style2.BorderRight = BorderStyle.Thick;
33             style2.BorderTop = BorderStyle.Thick;
34             style2.VerticalAlignment = VerticalAlignment.Center;
35 
36             //顶部厚边框
37             ICellStyle style3 = hssfworkbook.CreateCellStyle();
38             style3.BorderTop = BorderStyle.Thick;
39             style3.BorderRight = BorderStyle.Thin;
40             style3.VerticalAlignment = VerticalAlignment.Center;
41 
42             //右部厚边框
43             ICellStyle style4 = hssfworkbook.CreateCellStyle();
44             style4.BorderRight = BorderStyle.Thick;
45             style4.VerticalAlignment = VerticalAlignment.Center;
46 
47             //左部厚边框
48             ICellStyle style5 = hssfworkbook.CreateCellStyle();
49             style5.BorderLeft = BorderStyle.Thick;
50             style5.BorderRight = BorderStyle.Thin;
51             style5.VerticalAlignment = VerticalAlignment.Center;
52 
53             //底部厚边框
54             ICellStyle style6 = hssfworkbook.CreateCellStyle();
55             style6.BorderTop = BorderStyle.Thin;
56             style6.BorderBottom = BorderStyle.Thick;
57             style6.BorderRight = BorderStyle.Thin;
58             style6.VerticalAlignment = VerticalAlignment.Center;
59 
60             //文字上对齐
61             ICellStyle style7 = hssfworkbook.CreateCellStyle();
62             style7.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
63             style7.VerticalAlignment = VerticalAlignment.Top;
64 
65             #endregion

如何使用这些样式：

1 //设置第一行第一列单元格样式为style2
2 HSSFRow row = (HSSFRow)ws.CreateRow(0);
3 row.GetCell(0).CellStyle = style2;

合并单元格的方法

    /// <summary>
    /// 合并单元格
    /// </summary>
    private void mergeCell(HSSFSheet sheet, int firstRow, int lastRow, int firstCell, int lastCell)
    {
        sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCell, lastCell));
    }

在单元格中插入图片

 1     private void NpoiInsertPicture(IWorkbook workbook, ISheet sheet, string imagePath, int col1, int row1, int col2, int row2, int dx1, int dy1)
 2     {
 3         try
 4         {
 5             FileStream fs = new FileStream(imagePath, FileMode.Open, FileAccess.Read);
 6             byte[] Content = new byte[Convert.ToInt32(fs.Length)];
 7             fs.Read(Content, 0, Convert.ToInt32(fs.Length));
 8 
 9             int pictureIdx = workbook.AddPicture(Content, NPOI.SS.UserModel.PictureType.JPEG);
10             HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
11             // 参数的解析: HSSFClientAnchor（int dx1,int dy1,int dx2,int dy2,int col1,int row1,int col2,int row2)
12             //dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
13             //dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
14             //dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
15             //dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
16             //col1和row1 :图片左上角的位置，以excel单元格为参考,比喻这两个值为(1,1)，那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。
17             //col2和row2:图片右下角的位置，以excel单元格为参考,比喻这两个值为(2,2)，那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。
18 
19             HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, 0, 0, col1, row1, col2, row2);
20             HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
21         }
22         catch (Exception)
23         {
24         }
25 
26     }

 

转载于:https://www.cnblogs.com/zhangfeng1993/p/6158675.html