EPPlus导出Excel
//输入 存储地址 数据
public void Export(string fileUrl, DataTable dt)
{
FileInfo info = new FileInfo(fileUrl);
if (info.Exists)
{
info.Delete();
info = new FileInfo(fileUrl);
}
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
using (ExcelPackage package = new ExcelPackage(info))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("签到结果");
ListToExcel(worksheet, dt);
package.Save();
}
Console.WriteLine("导出完成");
}
//列表转为Excel
public void ListToExcel(ExcelWorksheet excelWorksheet, DataTable list, string photos_path)
{
//读取模板 用来设置列的宽高
FileInfo existingFile = new FileInfo("templete.xlsx");
ExcelPackage package = new ExcelPackage(existingFile);
ExcelWorksheets excelWorksheets = package.Workbook.Worksheets;
ExcelWorksheet sheet = excelWorksheets[0];
//定义Excel行数
int lineNumber = 1, columnNumber = 1;
//绘制Excel列表标题
columnNumber = 0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "考生号";
excelWorksheet.Column(columnNumber).Width =Convert.ToInt32( sheet.Columns[1].Width);
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "姓名";
excelWorksheet.Column(columnNumber).Width = Convert.ToInt32(sheet.Columns[5].Width);
excelWorksheet.Cells[lineNumber, 1, lineNumber, columnNumber].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//绘制Excel列表
lineNumber++;
foreach (DataRow items in list.Rows)
{
if (lineNumber > 100) break;
excelWorksheet.Row(lineNumber).Height = Convert.ToInt32(sheet.Rows[1].Height);
columnNumber = 0;
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["考生号"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["姓名"].ToString());
lineNumber++;
Console.WriteLine(lineNumber);
}
}
//绘制列表单元格
public void DrawExcelListCell(ExcelWorksheet excelWorksheet, int lineNumber, int columnNumber, string value)
{
using (ExcelRange range = excelWorksheet.Cells[lineNumber, columnNumber])
{
range.Value = value;
range.Style.Font.SetFromFont("宋体", 12f);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
}
}
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
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
上次更新: 2023/05/09, 17:54:37