用C#,将DataTable中的数据导出到Excel2007,求各位大神指导。。而且一定要有如下图的对话框进行提示。。

2024-12-15 21:13:18
推荐回答(1个)
回答1:

        /// 
        /// 导出Excel
        /// 

        /// 导出的数据源
        /// excel的名称
        /// excel的标题
        /// 
        public static void DataTableToExcel(DataTable dt, string title, string fName, HttpResponse resp)
        {
            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            IWorkbook workbook = application.Workbooks.Create(1);

            IWorksheet sheet = workbook.Worksheets[0];

            sheet.ImportDataTable(dt, true, 3, 1, -1, -1);

            //Header Style
            IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
            headerStyle.BeginUpdate();

            //Add custom colors to the palette.
            //workbook.SetPaletteColor(8, Color.FromArgb(182, 189, 218));
            headerStyle.Color = Color.DarkBlue;
            headerStyle.Font.Bold = true;
            headerStyle.Font.Color = ExcelKnownColors.White;
            headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
            headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;

            //Apply Style
            char[] constant = {
                                  'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
                                  'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','a','b','c','d','e','f','g','h','i', 'j', 'k', 'l',
                                  'm', 'n', 'o', 'p', 'q', 'r','s', 't', 'u', 'v', 'w', 'x', 'y', 'z'

                              };
            sheet.Range["A1:" + constant[dt.Columns.Count - 1] + "1"].CellStyleName = "HeaderStyle";
            sheet.Range["A3:" + constant[dt.Columns.Count - 1] + "3"].CellStyleName = "HeaderStyle";
            headerStyle.EndUpdate();

            //Autofit Rows and Columns
            //sheet.UsedRange.AutofitRows();
            sheet.UsedRange.AutofitColumns();

            sheet.Range["A1"].Text = title;
            sheet.Range["A1:" + constant[dt.Columns.Count - 1] + "1"].Merge();
            sheet.Range["A1"].CellStyle.Font.Size = 10;
            sheet.Range["A1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;

            //Saving the workbook to disk.
            workbook.SaveAs(HttpUtility.UrlEncode(fName, Encoding.UTF8), ExcelSaveType.SaveAsXLS, resp, ExcelDownloadType.PromptDialog);

            //No exception will be thrown if there are unsaved workbooks.
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
        }