95 lines
3.0 KiB
C#
95 lines
3.0 KiB
C#
using NPOI.SS.UserModel;
|
|
using NPOI.XSSF.UserModel;
|
|
using System.Data;
|
|
using System.IO;
|
|
|
|
namespace DeviceRepairAndOptimization.Utils
|
|
{
|
|
public static class ExcelExport
|
|
{
|
|
|
|
|
|
/// <summary>
|
|
/// DataTable 导出到 Excel
|
|
/// </summary>
|
|
/// <param name="dataTable">数据</param>
|
|
/// <returns></returns>
|
|
public static byte[] Export(this DataTable dataTable)
|
|
{
|
|
IWorkbook workbook = new XSSFWorkbook();
|
|
string sheetName = string.IsNullOrEmpty(dataTable.TableName) ? "Sheet1" : dataTable.TableName;
|
|
ISheet sheet = workbook.CreateSheet(sheetName);
|
|
|
|
// 创建表头
|
|
IRow headerRow = sheet.CreateRow(0);
|
|
for (int i = 0; i < dataTable.Columns.Count; i++)
|
|
{
|
|
headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
|
|
}
|
|
|
|
// 填充数据
|
|
for (int i = 0; i < dataTable.Rows.Count; i++)
|
|
{
|
|
IRow dataRow = sheet.CreateRow(i + 1);
|
|
for (int j = 0; j < dataTable.Columns.Count; j++)
|
|
{
|
|
dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
|
|
}
|
|
}
|
|
|
|
// 转换为byte[]
|
|
using (MemoryStream stream = new MemoryStream())
|
|
{
|
|
workbook.Write(stream);
|
|
return stream.ToArray();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// DataSet 导出到 Excel
|
|
/// </summary>
|
|
/// <param name="dataSet"></param>
|
|
/// <returns></returns>
|
|
public static byte[] Export(this DataSet dataSet)
|
|
{
|
|
IWorkbook workbook = new XSSFWorkbook();
|
|
|
|
|
|
if (dataSet == null || dataSet.Tables.Count == 0)
|
|
return null;
|
|
|
|
for (int i = 0; i < dataSet.Tables.Count; i++)
|
|
{
|
|
DataTable dataTable = dataSet.Tables[i];
|
|
string sheetName = string.IsNullOrEmpty(dataTable.TableName) ? "sheet" + (i + 1).ToString() : dataTable.TableName;
|
|
ISheet sheet = workbook.CreateSheet(sheetName);
|
|
|
|
// 创建表头
|
|
IRow headerRow = sheet.CreateRow(0);
|
|
for (int j = 0; j < dataTable.Columns.Count; j++)
|
|
{
|
|
headerRow.CreateCell(j).SetCellValue(dataTable.Columns[j].ColumnName);
|
|
}
|
|
|
|
// 填充数据
|
|
for (int j = 0; j < dataTable.Rows.Count; j++)
|
|
{
|
|
IRow dataRow = sheet.CreateRow(j + 1);
|
|
for (int k = 0; k < dataTable.Columns.Count; k++)
|
|
{
|
|
dataRow.CreateCell(k).SetCellValue(dataTable.Rows[j][k].ToString());
|
|
}
|
|
}
|
|
}
|
|
|
|
// 转换为byte[]
|
|
using (MemoryStream stream = new MemoryStream())
|
|
{
|
|
workbook.Write(stream);
|
|
return stream.ToArray();
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|