public void CreateExcelFile <T>(CreateExcelAction <T> createExcelAction, List <T> list, ExcelFormat excelFormat)
{
//建立Excel 2003檔案
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Class");
XSSFRow row = (XSSFRow)ws.CreateRow(0);
row.Height = 440;
ICellStyle positionStyle = wb.CreateCellStyle();
positionStyle.WrapText = true;
positionStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
positionStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
foreach (var item in excelFormat.ColumnFormats)
{
ws.SetColumnWidth(excelFormat.ColumnFormats.IndexOf(item), item.CoiumnWidth);
CreateCell(row, excelFormat.ColumnFormats.IndexOf(item), item.ColumnTitle, positionStyle);
}
int rowIndex = 1;
foreach (var storeData in list)
{
createExcelAction(wb, ws, positionStyle, ref rowIndex, storeData);
}
FileStream file = new FileStream(string.Concat(AppSettingConfig.FilePath(), @"\", excelFormat.FileName, DateTime.Now.ToString("yyyyMMdd"), ".xlsx"), FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
/// <summary>
/// 讀取Excel後匯出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="readExcelAction"></param>
/// <param name="createExcelAction"></param>
/// <param name="timeRange"></param>
/// <param name="excelFormat"></param>
public void InventoryCheckSheet <T>(ReadExcelAction <T> readExcelAction, CreateExcelAction <T> createExcelAction, int timeRange, ExcelFormat excelFormat)
{
IWorkbook workbook = null; //新建IWorkbook對象
string fileName = string.Concat(AppSettingConfig.FilePath(), "/", AppSettingConfig.StoreManageFileName());
FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook
var list = new List <T>();
for (int sheetCount = 1; sheetCount < workbook.NumberOfSheets; sheetCount++)
{
ISheet sheet = workbook.GetSheetAt(sheetCount); //獲取第i個工作表
IRow row;
var firstRow = sheet.GetRow(0);
for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++) //對工作表每一行
{
if (rowIndex > 70)
{
break;
}
row = sheet.GetRow(rowIndex); //row讀入第i行數據
if (row != null)
{
//該筆資料沒有顏色則不讀取該Row
if (row.GetCell(1) == null)
{
break;
}
readExcelAction(list, row, sheet.SheetName, timeRange);
}
else
{
break;
}
}
}
CreateExcelFile(createExcelAction, list, excelFormat);
}
