云智博客-blog

云智企业管理信息系统官方博客

NPOI在云智系统中的使用

2017-06-02 team 技术构架 (0)

在云智系统中,有许多的数据需要导入到excel,使用微软的office对于web来说要求对服务器的权限过高,所以我使用的第三方的开源软件NPOI.

下面的代码示例就是直接将DataTable转为Excel.表头通过titleArr进行定义.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/// <summary> 
/// 导出Excel 
/// </summary> 
/// <param name="Dt">数据源</param> 
/// <param name="ExcleName">导入文件名称</param> 
/// <param name="SheetName">工作薄名称</param> 
/// <param name="titleArr">标题栏</param> 
/// <param name="clumnArr">栏位名</param> 
public static void ResponseDataSetToExcel(DataTable Dt, string ExcleName, string SheetName, string[] titleArr, string[] clumnArr)
{
    HSSFWorkbook hssfworkbook = new HSSFWorkbook();
 
    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
    dsi.Company = "Ciemis";
    hssfworkbook.DocumentSummaryInformation = dsi;
 
    //create a entry of SummaryInformation 
    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
    si.Subject = " Export  Excel";
    hssfworkbook.SummaryInformation = si;
 
    HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet(SheetName);
    int rowCount = 0;
    HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0);
    rowCount++;
    //循环写出列头          
    for (int i = 0; i < titleArr.Length; i++)
    {
        HSSFCell newCell = (HSSFCell)newRow.CreateCell(i);
        newCell.SetCellValue(titleArr[i]);
    }
    for (int i = 0; i < Dt.Rows.Count; i++)
    {
        rowCount++;
        HSSFRow newRowData = (HSSFRow)excelSheet.CreateRow(rowCount);
        DataRow dr = Dt.Rows[i];
        for (int j = 0; j < clumnArr.Length; j++)
        {
            HSSFCell newCell = (HSSFCell)newRow.CreateCell(rowCount);
            newCell.SetCellValue(dr[titleArr[j]].ToString());
        }
    }
 
    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
    //避免乱码System.Text.Encoding.UTF8
    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(ExcleName, System.Text.Encoding.UTF8));
    HttpContext.Current.Response.Clear();
 
    MemoryStream file = new MemoryStream();
    hssfworkbook.Write(file);
 
    HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();
}

要导出一个DataSet,里面的多个DataTable对应Excel的多个Sheet;

且自动根据数据类型设置Excel的单元格格式.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
/// <summary>
/// 导出Excel 
/// </summary>
/// <param name="sourceDs"></param>
/// <param name="fileName">导出的文件名</param>
/// <param name="sheetName">导出的表名,多个表请以逗号(,)分割</param>
public static void ResponseDataSetToExcel(DataSet sourceDs, string fileName, string sheetName = null)
{
    fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8).ToString();
 
    HSSFWorkbook workbook = new HSSFWorkbook();
    MemoryStream ms = new MemoryStream();
    string[] sheetNames = (sheetName ?? string.Empty).Split(',');
    int length = sourceDs.Tables.Count;
    for (int i = 0; i < length; i++)
    {
        var _sheetName = sheetNames.Length > i ? sheetNames[i] : string.Format("Sheet{0}", i);
        var sheet = workbook.CreateSheet(_sheetName);
        sheet.CreateFreezePane(0, 1, 0, 1);
        //headStyle
        var headStyle = workbook.CreateCellStyle();
        IFont font = workbook.CreateFont();
        font.Boldweight = (short)FontBoldWeight.Bold;
        font.FontHeightInPoints = (short)11;
        font.Color = HSSFColor.Black.Index;
        headStyle.FillBackgroundColor = HSSFColor.Black.Index;
        //headStyle.FillPattern = FillPattern.SolidForeground;
        headStyle.SetFont(font);
        //cellStyleDet
        var cellStyleDet = workbook.CreateCellStyle();
        IFont font1 = workbook.CreateFont();
        font.FontHeightInPoints = (short)10;
        cellStyleDet.SetFont(font1);
 
        var headerRow = sheet.CreateRow(0);
        // handling header.
        foreach (DataColumn column in sourceDs.Tables[i].Columns)
        {
            var headCell = headerRow.CreateCell(column.Ordinal);
            headCell.SetCellValue(column.ColumnName);
            headCell.CellStyle = headStyle;
        }
        // handling value.
        int rowIndex = 1;
        foreach (DataRow row in sourceDs.Tables[i].Rows)
        {
            var dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in sourceDs.Tables[i].Columns)
            {
                var detCell = dataRow.CreateCell(column.Ordinal);
                var cellValue = row[column].ToString();
                var type = column.DataType.ToString().ToLower();
                if (row[column] != DBNull.Value)
                {
                    if (type.Contains("decimal") || type.Contains("double") || type.Contains("int"))
                    {
                        detCell.SetCellValue(Convert.ToDouble(row[column]));
                    }
                    else if (type.Contains("datetime"))
                    {
                        detCell.SetCellValue(((DateTime)row[column]).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        detCell.SetCellValue(row[column].ToString());
                    }
                }
                detCell.CellStyle = cellStyleDet;
            }
            rowIndex++;
        }
    }
    workbook.Write(ms);
 
    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
    HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();
 
    ms.Flush();
    ms.Position = 0;
    ms.Close();
    workbook = null;
    ms = null;
}

如果想对excel进行只读处理,不许对导出的数据进行修改,只需要加入一行代码:

1
sheet.ProtectSheet("password");
标签 :

第一个评分

  • Currently .0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
不允许评论