c# - Winform-导出到 Excel - XLWorkbook - 日期和时间 NumberFormat

问题描述

我的要求是——

我必须导出Datatable到.xls文件。
对于中的所有日期列Datatable，excel 单元格应该是Date格式而不是文本或常规。这是因为，用户可以根据需要根据日期过滤数据。

我的代码

            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            Workbook xlWorkBook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            StringBuilder sb = new StringBuilder();
            sb.Append("<html><body>");
            sb.Append("<table style='border: 1px solid black;'>");
            var index = 1;
            sb.Append("<tr>");
            sb.Append("<th colspan='9'>SANJAY MALAKIYA</th>");
            sb.Append("</tr>");
            sb.Append("<tr></tr>");
            sb.Append("<tr>");
            sb.Append("<td colspan='9'><b>SANJAY</td>");
            sb.Append("</tr>");
            sb.Append("<tr>");
            sb.Append("<td style='border: 1px solid black'>SrNO</td>");
            foreach (DataColumn column in dtDataTable1.Columns)
            {
                sb.Append("<td style='border: 1px solid black'>" + column.ColumnName + "</td>");
            }
            sb.Append("</tr>");
            ExcelApp.Windows.Application.ActiveWindow.DisplayGridlines = false;
            StringBuilder objSB = new StringBuilder();

            foreach (DataRow row in dtDataTable1.Rows)
            {
                sb.Append("<tr>");

                sb.Append("<td style='width:100px;border: 1px solid black'>" + index.ToString() + "</td>");
                foreach (DataColumn column in dtDataTable1.Columns)
                {
                    sb.Append("<td style='width:100px;border: 1px solid black'>" + row[column.ColumnName].ToString() + "</td>");
                }
                index++;
                sb.Append("</tr>");
            }
            if (dtDataTable1.Rows.Count > 0)
            {


                ExcelApp.Columns.AutoFit();

            }
            sb.Append("</table>");
            sb.Append("</body></html>");
            String Todaysdate = DateTime.Now.ToString("dd-MM-yyyy");
            if (!Directory.Exists("C:\\Users\\Krupal\\Desktop\\" + Todaysdate))
            {
                Directory.CreateDirectory("C:\\Users\\Krupal\\Desktop\\" + Todaysdate);
            }
            using (System.IO.StreamWriter file = new System.IO.StreamWriter("C:\\Users\\Krupal\\Desktop\\" + Todaysdate + "\\Summury_final " + Convert.ToDateTime(cmbmonth.SelectedItem).ToString("dd/MM/yyyy") + ".XLS"))
            {
                file.WriteLine(sb.ToString());
            }
            Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
            Workbook xlWorkBook1 = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            xlWorkBook1 = Excel.Workbooks.Open("C:\\Users\\Krupal\\Desktop\\" + Todaysdate + "\\Summury_final " + Convert.ToDateTime(cmbmonth.SelectedItem).ToString("dd/MM/yyyy") + ".XLS", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel.Worksheet Wt = (Excel.Worksheet)xlWorkBook1.Worksheets.get_Item(1);
            Excel.Visible = true;

标签： c#excelwindows

c# - Winform-导出到 Excel - XLWorkbook - 日期和时间 NumberFormat

问题描述

解决方案

推荐阅读