using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Business;using System.Runtime.InteropServices;using System.Data.OleDb;using Microsoft.Office.Interop.Excel;/// <summary>/// ExportToExcels 的摘要说明/// </summary>public class ExportToExcels{ public ExportToExcels() { // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 导出到 Excel 文件 /// </summary> /// <param name=”rs”>当前记录</param> /// <param name=”flds”>要导出的字段</param> /// <param name=”fn”>文件的路径文件名</param> /// <param name=”dcfg”>词典配置工具</param> public static void ExportToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn, DictionaryConfigure dcfg) { Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 建立Excel对象 excel.Application.Workbooks.Add(true); // Excel表为添加状态 //for (int i = 0; i < flds.Length; i++) // 填充表头 //{ // excel.Cells[1, i + 1] = flds[i].Name; //} excel.Columns.EntireColumn.AutoFit();//列宽自适应。 excel.Cells[1, 1] = “2012年省级政府投资项目储备库项目申报汇总表”; Microsoft.Office.Interop.Excel.Range r; r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 19]); //取得合并的区域 r.MergeCells = true; // 设置整个报表的标题格式 //excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Bold = true; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 18; // 设置整个报表的标题为跨列居中 excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Select(); excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter; excel = setExcel(excel, “填报单位”, 2, 1, 3, 1, false, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “项目类别”, 2, 2, 3, 2, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “项目名称”, 2, 3, 3, 3, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “建设性质”, 2, 4, 3, 4, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “建设起止年限”, 2, 5, 3, 5, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “建设结束年限”, 2, 6, 3, 6, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “项目建设单位”, 2, 7, 3, 7, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “建设规模及主要内容”, 2, 8, 3, 8, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “总投资（万元）”, 2, 9, 3, 9, true, 58, 20, XlHAlign.xlHAlignCenter); //excel = setExcel(excel, “拟申请省级财政性资金”, 2, 10, 2, 13, true, 58, 20, XlHAlign.xlHAlignCenter); //excel = setExcel(excel, “共计（万元）”, 3, 10, 3, 10, false, 58, 20, XlHAlign.xlHAlignCenter); //excel = setExcel(excel, “申请年度”, 3, 11, 3, 11, false, 58, 20, XlHAlign.xlHAlignCenter); //excel = setExcel(excel, “拟申请省级财政性专项资金名称”, 3, 12, 3, 12, false, 58, 20, XlHAlign.xlHAlignCenter); //excel = setExcel(excel, “拟申请金额（万元）”, 3, 13, 3, 13, false, 58, 20, XlHAlign.xlHAlignCenter); //excel = setExcel(excel, “拟申请省级财政性资金”, 2, 10, 2, 13, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “共计（万元）”, 2, 10, 3, 10, false, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “申请年度”, 2, 11, 3, 11, false, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “拟申请省级财政性专项资金名称”, 2, 12, 3, 12, false, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “拟申请金额（万元）”, 2, 13, 3, 13, false, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “项目审批文号”, 2, 14, 3, 14, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “项目进展情况”, 2, 15, 3, 15, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “项目用地情况(亩)”, 2, 16, 3, 16, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “项目建设必要性”, 2, 17, 3, 17, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “产业政策、城乡规划、土地利用规划符合性”, 2, 18, 3, 18, true, 58, 20, XlHAlign.xlHAlignCenter); excel = setExcel(excel, “处理状态”, 2, 19, 3, 19, true, 58, 20, XlHAlign.xlHAlignCenter); for (int i = 0; i < rs.Length; i++) // 填充数据 { for (int j = 0; j < flds.Length; j++) { string txt = rs[i].getStringValue(flds[j].ID); if (!flds[j].DictionaryID.Equals(“”)) { txt = Business.DictionaryConfigure.GetCatalogNameByDictionary(txt, dcfg.getDictionary(flds[j].DictionaryID)); } if (flds[j].EditIsDate) { txt = (txt.Length > 8 ? txt.Substring(0, 8) : txt); txt = Utility.StringUtility.Alt(txt, “????-??-??”, “?”); } excel.Cells[i + 3, j + 1] = txt; } } excel.Visible = false; excel.DisplayAlerts = false; excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); KillExcelProcess(excel); } public static Microsoft.Office.Interop.Excel.ApplicationClass setExcel(Microsoft.Office.Interop.Excel.ApplicationClass excel, string title, int xNum, int yNum, int xNum2, int yNum2,bool isMerge, int height, int width, XlHAlign xl) { excel.Cells[xNum, yNum] = title; Microsoft.Office.Interop.Excel.Range r; r = excel.get_Range(excel.Cells[xNum, yNum], excel.Cells[xNum2, yNum2]); //r.MergeCells = true; r.Merge(isMerge); //r.ColumnWidth = width; //r.RowHeight = height; r.HorizontalAlignment = xl; r.WrapText = true; return excel; } /// <summary> /// 获取进程标识 /// </summary> /// <param name=”hwnd”>输入参数：句柄</param> /// <param name=”ID”>输出参数：进程标识</param> /// <returns></returns> [DllImport(“User32.dll”, CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); /// <summary> /// 关闭 Excel 进程 /// </summary> /// <param name=”excel”></param> private static void KillExcelProcess(Microsoft.Office.Interop.Excel.ApplicationClass excel) { //———— 方法 1 —————– //excel.Quit(); //excel = null; //GC.Collect(); // 垃圾回收 //———— 方法 2 —————– try { IntPtr handler = new IntPtr(excel.Hwnd); // 句柄 int processid = 0; // 进程标识 GetWindowThreadProcessId(handler, out processid); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(processid); // 进程 p.Kill(); // 杀除进程 } catch { excel.Workbooks.Close(); excel.Quit(); } //—————————– } /// <summary> /// 从指定的Excel文件导入 /// </summary> /// <param name=”strFileName”>导入文件</param> /// <returns></returns> public DataSet importFromExcel(string strFileName) { if (strFileName == “”) return null; string strConn = ” Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = ” + strFileName + “;Extended Properties=Excel 8.0”; OleDbDataAdapter excelDA = new OleDbDataAdapter(“select * from [Sheet1$]”, strConn); // 连接字符串 DataSet ds = new DataSet(); // 建立数据集，用于存放导入Excel的数据 excelDA.Fill(ds, “ExcelInfo”); // 填充DataSet return ds; } /// <summary> /// 将记录集导出到 Excel 文件 /// </summary> /// <param name=”rs”>当前记录集</param> /// <param name=”flds”>要导出的字段</param> /// <param name=”fn”>文件的路径文件名</param> public static void RecordsToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn) { Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 建立Excel对象 excel.Application.Workbooks.Add(true); // Excel表为添加状态 for (int i = 0; i < flds.Length; i++) // 填充表头 { excel.Cells[1, i + 1] = flds[i].Name; } for (int i = 0; i < rs.Length; i++) // 填充数据 { for (int j = 0; j < flds.Length; j++) { excel.Cells[i + 2, j + 1] = rs[i].getStringValue(flds[j].ID); } } excel.Visible = false; excel.DisplayAlerts = false; excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); KillExcelProcess(excel); }}
支付宝扫一扫
微信扫一扫
