/// <summary>
/// Khoi tao file excel khong chua bat ky sheet nao
/// </summary>
/// <param name="spreadsheetDocument"></param>
public CWorkbook(SpreadsheetDocument spreadsheetDocument)
{
_spreadsheetDocument = spreadsheetDocument;
//create workbook
_workbookPart = _spreadsheetDocument.AddWorkbookPart();
_workbook = _spreadsheetDocument.WorkbookPart.Workbook = new Workbook();
_sharedStringTablePart = SharedStringTPGet(_workbookPart);
_sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
//tao init style: cac the dau tien phuc vu add style. Ca file excel chi co mot tap hop the style
WorkbookStylesPart stylesPart = _workbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = CStyle.GenerateStyleSheet();
stylesPart.Stylesheet.Save();
}
/// <summary>
/// Xuat mot so row lua chon
/// </summary>
/// <param name="spreadsheetDoc"></param>
/// <param name="sheetName"></param>
/// <param name="table"></param>
/// <param name="sheetData"></param>
/// <param name="chs"></param>
public void DatatableExport(WorkbookPart workbookPart,
DataTable table,
SheetData sheetData,
List <ColunmHelper> chs,
SharedStringTablePart sharedStringTablePart
)
{
foreach (DataRow dr in table.Rows)
{
Row row = RowAddNew(sheetData);
foreach (DataColumn column in table.Columns)
{
foreach (ColunmHelper ch in chs)
{
if (column.ColumnName == ch.CValue)
{
Cell cell = new Cell();
int index;
string numberFormat = null;
if (ch.CType == 1)
{
index = InsertSharedStringItem_Format(dr[column].ToString(), sharedStringTablePart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else if (ch.CType == 2)
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(dr[column].ToString());
numberFormat = "#,##0";
}
else if (ch.CType == 3)
{
cell.DataType = CellValues.Date;
cell.CellValue = new CellValue(dr[column].ToString());
}
//style for cell
CStyle cStyle = new CStyle();
if (ch._cAlignment == 1) //left
{
cStyle.CreateStyle(numberFormat, 12, "TimeNewRoman", "#000", true, true, PatternValues.None, null, "#000", BorderStyleValues.Thin,
"#000", BorderStyleValues.Dotted,
"#000", BorderStyleValues.DashDot,
"#000", BorderStyleValues.Hair, HorizontalAlignmentValues.Right, VerticalAlignmentValues.Center);
}
else if (ch._cAlignment == 2)//center
{
cStyle.CreateStyle(numberFormat, 12, "TimeNewRoman", "#000", true, true, PatternValues.None, null, "#000", BorderStyleValues.Thin,
"#000", BorderStyleValues.Dotted,
"#000", BorderStyleValues.DashDot,
"#000", BorderStyleValues.Hair, HorizontalAlignmentValues.Right, VerticalAlignmentValues.Center);
}
else if (ch._cAlignment == 3) //right
{
cStyle.CreateStyle(numberFormat, 12, "TimeNewRoman", "#000", true, true, PatternValues.None, null, "#000", BorderStyleValues.Thin,
"#000", BorderStyleValues.Dotted,
"#000", BorderStyleValues.DashDot,
"#000", BorderStyleValues.Hair, HorizontalAlignmentValues.Right, VerticalAlignmentValues.Center);
}
CellFormat cellFormat = cell.StyleIndex != null?cStyle.GetCellFormat(cell.StyleIndex, workbookPart).CloneNode(true) as CellFormat : new CellFormat();
cStyle.AppendCellFormat(cellFormat, workbookPart);
uint styleIndex = cStyle.InsertCellFormat(cellFormat, workbookPart);
cell.StyleIndex = styleIndex;
row.AppendChild(cell);
break;
}
}
}
}
}