/// <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; } } } } }