DocumentFormat.OpenXml and Excel Export (edit)
Vấn đề xuất Excel mà cần phải chỉnh lại format cho dữ liệu kiểu số, kiểu ngày (date), kiểu giờ (time) và kiểu ngày + giờ (datetime), sẽ được giải quyết qua đoạn code dưới
SQL Server
SELECT CONVERT(VARCHAR(10), CreatedTimeStamp, 101) + ' '
+ LTRIM(RIGHT(CONVERT(CHAR(20), CreatedTimeStamp, 22), 11)) as [CreatedTimeStamp_MMddyyyy_12H_AMPM]
,CONVERT(VARCHAR(10), ModifiedTimeStamp, 101) + ' '
+ LTRIM(RIGHT(CONVERT(CHAR(20), ModifiedTimeStamp, 22), 11)) as [ModifiedTimeStamp_MMddyyyy_12H_AMPM]
,CONVERT(VARCHAR(10), CreatedTimeStamp, 103) + ' '
+ LTRIM(RIGHT(CONVERT(CHAR(20), CreatedTimeStamp, 22), 11)) as [CreatedTimeStamp_ddMMyyyy_12H_AMPM]
,CONVERT(VARCHAR(10), ModifiedTimeStamp, 103) + ' '
+ LTRIM(RIGHT(CONVERT(CHAR(20), ModifiedTimeStamp, 22), 11)) as [ModifiedTimeStamp_ddMMyyyy_12H_AMPM]
From [dbo].[User]
declare @dt DateTime = GETDATE()
select CONVERT(VARCHAR(10), @dt, 101) --12/07/2018
select CONVERT(VARCHAR(10), @dt, 103) --07/12/2018
select CONVERT(VARCHAR(10), @dt, 105) --07-12-2018
select CONVERT(VARCHAR(10), @dt, 110) --12-07-2018
Custom Format Cells - Excel Example
Kiểu ngày trong Excel: dd/mm/yyyy
Kiểu giờ trong Excel: hh:mm:ss AM/PM
Kiểu ngày + giờ trong Excel: dd/mm/yyyy hh:mm:ss AM/PM
1) Lấy dữ liệu từ cơ sở dữ liệu lên
private DataTable GetDataTableFromDatabase()
{
string constring = @"Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=123456";
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].[User]", con))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
}
private DataTable GetDataTableFromDatabase2()
{
string sqlConnectionString = @"Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=123456";
using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
{
sqlConnection.Open();
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT * FROM [dbo].[User]", sqlConnection))
{
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
return dataTable;
}
}
}
Sự kiện Page_Load
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.Tables.Add(GetDataTableFromDatabase());
string m_DateFormat = "MM/dd/yyyy";
string m_TimeFormat = "hh:mm:ss AM/PM"; //TODO: MANH
GenerateExcel(ds, m_DateFormat, m_TimeFormat);
}
OpenXml và định dạng dữ liệu
public void GenerateExcel(DataSet data, string m_DateFormat, string m_TimeFormat)
{
var memoryStream = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
WorkbookStylesPart wbsp = workbookPart.AddNewPart();
wbsp.Stylesheet = CreateStylesheet1(m_DateFormat, m_TimeFormat);
wbsp.Stylesheet.Save();
uint sheetId = 0;
foreach (System.Data.DataTable table in data.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
int k = sheets.ChildElements.Count;
string bsd = sheets.InnerText;
string jknkj = sheets.InnerXml;
sheetId++; //TODO: MANH
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List columns = new List();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DateTime dDate;
if (DateTime.TryParse(dsrow[col].ToString(), out dDate))
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.StyleIndex = 1;//TODO: MANH
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dDate.ToOADate().ToString());
newRow.AppendChild(cell);
}
else
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
}
sheetData.AppendChild(newRow);
}
}
workbookPart.Workbook.Save();
workbook.Close();
}
string fileName = @"D:\\Excel_Samples\File_" + DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") + ".xlsx";
Directory.CreateDirectory(Path.GetDirectoryName(fileName));
using (FileStream file = new FileStream(fileName, FileMode.Create))
{
memoryStream.WriteTo(file);
file.Close();
memoryStream.Close();
}
}
private Stylesheet CreateStylesheet1(string m_DateFormat, string m_TimeFormat)
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 1;//TODO: MANH
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
string formatCodeDateTime = string.Format("{0} {1}", m_DateFormat, m_TimeFormat);//TODO: MANH
nf.FormatCode = StringValue.FromString(formatCodeDateTime);//TODO: MANH
//nf.FormatCode = StringValue.FromString("mm-d-yy h:mm:ss AM/PM");//TODO: MANH
nfs.Append(nf);
cf = new CellFormat();
cf.ApplyNumberFormat = true;
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.0000";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.00";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// @ is also Excel style index 49
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "@";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return ss;
}
