public void Write(TestTable table, string outFile)
{
var pck = new OfficeOpenXml.ExcelPackage();
var sheet = pck.Workbook.Worksheets.Add("data");
const string dateFormat = "yyyy/mm/dd";
for (int r = 1; r <= table.RowCount; r++)
{
var datarow = table.GetNextRow();
OfficeOpenXml.ExcelRange cell;
var colNum = 1;
//STRING-column
//col1
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col1;
colNum++;
//col2
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col2;
colNum++;
//col3
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col3;
colNum++;
//col4
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col4;
colNum++;
//col5
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col5;
colNum++;
//NUMBER-column
//col6
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col6;
colNum++;
//col7
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col7;
colNum++;
//col8
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col8;
colNum++;
//col9
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col9;
colNum++;
//col10
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col10;
colNum++;
//DATE-column
//col11
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col11;
cell.Style.Numberformat.Format = dateFormat;
colNum++;
//col12
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col12;
cell.Style.Numberformat.Format = dateFormat;
colNum++;
//col13
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col13;
cell.Style.Numberformat.Format = dateFormat;
colNum++;
//col14
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col14;
cell.Style.Numberformat.Format = dateFormat;
colNum++;
//col15
cell = sheet.Cells[r, colNum];
cell.Value = datarow.Col15;
cell.Style.Numberformat.Format = dateFormat;
colNum++;
}
//AutoFit
for (var i = 1; i <= table.ColCount; i++)
{
sheet.Column(i).AutoFit();
}
//border-style
{
var border = sheet.Cells[1, 1, table.RowCount, table.ColCount].Style.Border;
border.Top.Style = ExcelBorderStyle.Thin;
border.Left.Style = ExcelBorderStyle.Thin;
border.Bottom.Style = ExcelBorderStyle.Thin;
border.Right.Style = ExcelBorderStyle.Thin;
}
FileInfo outFileInfo = new FileInfo(outFile);
pck.SaveAs(outFileInfo);
}
IEnumerable<RowDfn> GetRowsEnum(TestTable table)
{
var border = CellStyleBorder.CreateBoxBorder(CellStyleBorder.Thin);
var defaultCellStyle = new CellStyleDfn
{
Border = border,
};
var dateCellStyle = new CellStyleDfn
{
Border = border,
NumFmt = new CellStyleNumFmt { formatCode = "yyyy/mm/dd" },
};
int rowCount = table.RowCount;
var rows = new List<RowDfn>();
for (int r = 0; r < rowCount; r++)
{
var datarow = table.GetNextRow();
var cells = new CellDfn[]
{
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col1 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col2 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col3 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col4 },
new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col5 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col6 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col7 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col8 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col9 },
new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col10 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col11 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col12 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col13 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col14 },
new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col15 },
};
var row = new RowDfn { Cells = cells };
//yield return row;
rows.Add(row);
}
return rows.ToArray();
}
public void Write(TestTable table, string outFile)
{
var ws = new WorksheetDfn
{
Name = "data",
Cols = new ColDfn[]
{
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
new ColDfn { AutoFit = new ColAutoFit() },
},
Rows = GetRowsEnum(table),
};
WorkbookDfn wb = new WorkbookDfn
{
Worksheets = new WorksheetDfn[]
{
ws,
},
};
SpreadsheetWriter.Write(outFile, wb);
}
public void Write(TestTable table, string outFile)
{
var book = new XSSFWorkbook();
var sheet = book.CreateSheet("data");
//BorderStyle
var borderStyle = book.CreateCellStyle();
borderStyle.BorderTop = BorderStyle.Thin;
borderStyle.BorderLeft = BorderStyle.Thin;
borderStyle.BorderBottom = BorderStyle.Thin;
borderStyle.BorderRight = BorderStyle.Thin;
//style for DATE-Cell
var dateStyle = book.CreateCellStyle();
dateStyle.BorderTop = BorderStyle.Thin;
dateStyle.BorderLeft = BorderStyle.Thin;
dateStyle.BorderBottom = BorderStyle.Thin;
dateStyle.BorderRight = BorderStyle.Thin;
dateStyle.DataFormat = book.GetCreationHelper().CreateDataFormat().GetFormat("yyyy/mm/dd");
for (int r = 0; r < table.RowCount; r++)
{
var datarow = table.GetNextRow();
var destRow = sheet.CreateRow(r);
ICell cell;
var cellType = CellType.Blank;
var colNum = 0;
//STRING-Column
//col1
cellType = CellType.String;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col1);
cell.CellStyle = borderStyle;
colNum++;
//col2
cellType = CellType.String;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col2);
cell.CellStyle = borderStyle;
colNum++;
//col3
cellType = CellType.String;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col3);
cell.CellStyle = borderStyle;
colNum++;
//col4
cellType = CellType.String;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col4);
cell.CellStyle = borderStyle;
colNum++;
//col5
cellType = CellType.String;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col5);
cell.CellStyle = borderStyle;
colNum++;
//NUMBER-Column
//col6
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col6);
cell.CellStyle = borderStyle;
colNum++;
//col7
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col7);
cell.CellStyle = borderStyle;
colNum++;
//col8
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col8);
cell.CellStyle = borderStyle;
colNum++;
//col9
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col9);
cell.CellStyle = borderStyle;
colNum++;
//col10
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col10);
cell.CellStyle = borderStyle;
colNum++;
//DATE-Column
//col11
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col11);
cell.CellStyle = dateStyle;
colNum++;
//col12
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col12);
cell.CellStyle = dateStyle;
colNum++;
//col13
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col13);
cell.CellStyle = dateStyle;
colNum++;
//col14
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col14);
cell.CellStyle = dateStyle;
colNum++;
//col15
cellType = CellType.Numeric;
cell = destRow.CreateCell(colNum);
cell.SetCellType(cellType);
cell.SetCellValue(datarow.Col15);
cell.CellStyle = dateStyle;
colNum++;
}
//AutoFit
for (var i = 0; i < table.ColCount; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream streamw = File.Open(outFile, FileMode.Create))
{
book.Write(streamw);
}
}
public void Write(TestTable table, string outFile)
{
// no support
throw new NotImplementedException();
}