深度优先

这个家伙好懒，除了文章什么都没留下

0%

【常用代码】将IList对象集合导出Excel

发表于 分类于
本文字数： 6k 阅读时长 ≈ 5 分钟

最近项目中有许多地方需要将数据导出成Excel文件，因此写了个公共模块做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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
/// <summary>
/// 将IList对象导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="columnTitle"></param>
/// <param name="title"></param>
/// <param name="path"></param>
/// <returns></returns>
public string DataToExcel<T>(IList<T> list, List<DataKeyValue> columnTitle, string title,string path)
    where T : class
{
    DataTable dt = ListToDataTable(list);
    HSSFWorkbook book = new HSSFWorkbook();
    HSSFSheet sheet = book.CreateSheet("Sheet1") as HSSFSheet;

    HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
    dataRow = sheet.CreateRow(0) as HSSFRow;
    CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnTitle.Count - 1);
    sheet.AddMergedRegion(region);
    IRow hrow = sheet.CreateRow(0);
    hrow.Height = 20 * 20;
    ICell icellltop0 = hrow.CreateCell(0);

    IFont font12 = book.CreateFont();
    font12.FontHeightInPoints = 12;
    font12.FontName = "微软雅黑";
    font12.Boldweight = short.MaxValue;
    font12.Color = Black.Index;

    ICellStyle cellStyle = book.CreateCellStyle();
    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
    cellStyle.BorderBottom = BorderStyle.Thin;
    cellStyle.BorderLeft = BorderStyle.Thin;
    cellStyle.BorderRight = BorderStyle.Thin;
    cellStyle.BorderTop = BorderStyle.Thin;
    for (int i = region.FirstRow; i <= region.LastRow; i++)
    {
        IRow row = HSSFCellUtil.GetRow(i, sheet);
        for (int j = region.FirstColumn; j <= region.LastColumn; j++)
        {
            ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
            singleCell.CellStyle = cellStyle;
        }
    }

    cellStyle.SetFont(font12);
    cellStyle.FillBackgroundColor = BlueGrey.Index;
    icellltop0.CellStyle = cellStyle;
    icellltop0.SetCellValue(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"));

    ICellStyle TitleStyle = book.CreateCellStyle();
    TitleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
    TitleStyle.FillPattern = FillPattern.SolidForeground;
    TitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
    TitleStyle.BorderBottom = BorderStyle.Thin;
    TitleStyle.BorderLeft = BorderStyle.Thin;
    TitleStyle.BorderRight = BorderStyle.Thin;
    TitleStyle.BorderTop = BorderStyle.Thin;

    IRow TitleRow = sheet.CreateRow(1);
    TitleRow.Height = 20 * 15;
    for (int i = 0; i < columnTitle.Count; i++)
    {
        ICell cell = TitleRow.CreateCell(i);
        cell.CellStyle = TitleStyle;
        cell.SetCellValue(columnTitle[i].Value);
        sheet.SetColumnWidth(i, columnTitle[i].ColumnWidth);
    }

    // data
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        IRow row = sheet.CreateRow(i + 2);
        for (int j = 0; j < dt.Columns.Count; j++)
        {
        }
        for (int j = 0; j < columnTitle.Count; j++)
        {
            row.CreateCell(j).SetCellValue(dt.Rows[i][columnTitle[j].key].ToString());
        }
    }

    ICellStyle borStyle = book.CreateCellStyle();
    borStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
    borStyle.BorderBottom = BorderStyle.Thin;
    borStyle.BorderLeft = BorderStyle.Thin;
    borStyle.BorderRight = BorderStyle.Thin;
    borStyle.BorderTop = BorderStyle.Thin;

    for (int i = 1; i <= dt.Rows.Count+1; i++)
    {
        IRow row = HSSFCellUtil.GetRow(i, sheet);
        row.Height = 20 * 16;
        for (int j = 0; j < columnTitle.Count; j++)
        {
            ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
            singleCell.CellStyle = borStyle;
        }
    }
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    string fileName = title + DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
    using (FileStream fsWrite = File.OpenWrite(path + fileName))
    {
        book.Write(fsWrite);
    }
    return fileName;
}

IList To DataTable:

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
/// <summary>
/// 实体列表转换成DataTable
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="list"> 实体列表</param>
/// <returns></returns>
public DataTable ListToDataTable<T>(IList<T> list)
    where T : class
{
    if (list == null || list.Count <= 0)
    {
        return null;
    }
    DataTable dt = new DataTable(typeof(T).Name);
    DataColumn column;
    DataRow row;

    PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

    int length = myPropertyInfo.Length;
    bool createColumn = true;

    foreach (T t in list)
    {
        if (t == null)
        {
            continue;
        }
        row = dt.NewRow();
        for (int i = 0; i < length; i++)
        {
            PropertyInfo pi = myPropertyInfo[i];
            string name = pi.Name;
            if (createColumn)
            {
                column = new DataColumn(name, typeof(string));
                dt.Columns.Add(column);
            }
            row[name] = pi.GetValue(t,null) is null ?"": pi.GetValue(t, null).ToString();
        }
        if (createColumn)
        {
            createColumn = false;
        }
        dt.Rows.Add(row);
    }
    return dt;
}

配置列信息：

1
2
3
4
5
6
public class DataKeyValue
{
    public string key { get; set; }
    public string Value { get; set; }
    public int ColumnWidth { get; set; }
}

调用方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ExcelHelper excelHelper = new ExcelHelper();
List<DataKeyValue> list = new List<DataKeyValue>() {
    new DataKeyValue(){key="ID",Value="编号",ColumnWidth=256*10},
    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*20},
    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*20},
    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*10},
    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*35},

    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
    new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
};
string path = ConfigurationManager.AppSettings["ExportPath"].ToString();
string result = excelHelper.DataToExcel(allCustomerList, list, "Customer_", path);