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

Happy Coding!