查询数据集导入到Execl

来源：互联网发布：淘宝115会员暗号编辑：程序博客网时间：2024/05/22 15:05

List<PrintExcelCell> dicSource//查询出来的数据集

private static void ExportRptFormExcelTmp(Page contentPage, string templateFullName, List<PrintExcelCell> dicSource)
        {
            Microsoft.Office.Interop.Excel.Application xlsApp = null;
            Microsoft.Office.Interop.Excel.Workbook newBook = null;
            Microsoft.Office.Interop.Excel.Worksheet newSheet = null;
            System.Diagnostics.Process p = null;

            string guid = Guid.NewGuid().ToString();
            try
            {
                //打开Excel
                xlsApp = new Microsoft.Office.Interop.Excel.Application();
                xlsApp.Visible = false;
                xlsApp.AlertBeforeOverwriting = false;
                xlsApp.AskToUpdateLinks = false;
                xlsApp.DisplayAlerts = false;
                //新建空文档
                xlsApp.Workbooks.Add(contentPage.Server.MapPath(string.Format("~\\Print\\template\\{0}", templateFullName)));

                //激活当前工作簿
                newBook = xlsApp.ActiveWorkbook;
                newSheet = (Microsoft.Office.Interop.Excel.Worksheet)newBook.Sheets[1];

                foreach (PrintExcelCell pos in dicSource)
                {
                    Microsoft.Office.Interop.Excel.Range tmpRange = newSheet.Cells[pos.X, pos.Y] as Microsoft.Office.Interop.Excel.Range;
                    tmpRange.Value = pos.CellValue;
                    if (!string.IsNullOrEmpty(pos.CellStyleName))
                        tmpRange.Style = pos.CellStyleName;
                }
                //保存产生的excel
                string savePath = contentPage.Server.MapPath("~/Print/");

                //判断office版本
                string strVer = xlsApp.Version;
                decimal ver = Convert.ToDecimal(strVer);
                if (ver >= 12)
                    newBook.SaveAs(savePath + guid + ".xlsx", Type.Missing, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                else
                    newBook.SaveAs(savePath + guid + ".xls", Type.Missing, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                //打开生成Excel文件
                newBook.Close(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                xlsApp.Quit();
                if (p != null)
                {
                    if (p.ProcessName.ToUpper() == "EXCEL.EXE" && !p.HasExited)
                        p.Kill();
                }
                if (ver >= 12)
                    contentPage.Response.Redirect("~/Print/" + guid + ".xlsx", false);
                else
                    contentPage.Response.Redirect("~/Print/" + guid + ".xls", false);
            }
            catch (Exception ex)
            {
                if (xlsApp != null)
                    xlsApp.Quit();
                throw ex;
            }

}