using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using NPOI.SS.UserModel;
|
using NPOI.HSSF.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System.Data;
|
using System.IO;
|
|
namespace HotelPms.GrpcService.Util
|
{
|
public class Excel
|
{
|
public static byte[] Export(DataTable dt, string type = "xlsx")
|
{
|
const int headerRowIndex = 0;
|
|
// ブック作成
|
IWorkbook book;
|
switch (type)
|
{
|
case "xls":
|
book = new HSSFWorkbook();
|
break;
|
case "xlsx":
|
book = new XSSFWorkbook();
|
break;
|
default:
|
return null;
|
}
|
|
// シート作成
|
var sheet = book.CreateSheet("Table");
|
|
// ヘッダ行作成
|
var headerRow = sheet.CreateRow(0);
|
foreach (DataColumn col in dt.Columns)
|
{
|
ICell cell = headerRow.CreateCell(dt.Columns.IndexOf(col));
|
cell.SetCellValue(col.ColumnName);
|
}
|
|
// データ行作成
|
foreach (DataRow row in dt.Rows)
|
{
|
var dataRow = sheet.CreateRow(headerRowIndex + 1 + dt.Rows.IndexOf(row));
|
|
foreach (DataColumn col in dt.Columns)
|
{
|
var colIndex = dt.Columns.IndexOf(col);
|
if (row[colIndex] == DBNull.Value) continue;
|
|
ICell cell = dataRow.CreateCell(colIndex);
|
cell.SetCellValue(row[colIndex].ToString());
|
}
|
}
|
|
// ブックを出力（保存）
|
using (var ms = new MemoryStream())
|
{
|
book.Write(ms, true);
|
byte[] data = ms.ToArray();
|
ms.Close();
|
return data;
|
}
|
}
|
}
|
}
|
