/// <summary> /// 导出EXCEL /// </summary> /// <param name="ds"></param> /// <param name="fileName"></param> public static void ExportToExcel(DataSet ds, string fileName) { OleDbConnection conn = null; try { //加工文件名 string[] strTemp = fileName.Split('\\'); string NewName = string.Empty; foreach (string s in strTemp) { NewName = NewName + "\\\\" + s; } NewName = NewName.TrimStart('\\'); if (ds.Tables.Count == 0) { return; } Excel._Application xlApp = new Excel.ApplicationClass(); Excel._Workbook xlBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); if (ds.Tables.Count > 1) { int i = ds.Tables.Count; while (i > 1) { xlBook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); i--; } } int loop = 1; foreach (DataTable dt2 in ds.Tables) { FillExcelCaption((Excel._Worksheet)xlBook.Worksheets[loop], dt2); loop++; } xlBook.SaveCopyAs(fileName); xlBook.Saved = true; xlApp.Quit(); Kill((Excel.Application)xlApp); //取数据表 //string con = string.Format("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}; Extended Properties = Excel 8.0", NewName); //conn = new OleDbConnection(con); //conn.Open(); ExcelConn(NewName, ref conn); System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; foreach (DataTable dt in ds.Tables) { StringBuilder sql = new StringBuilder(); sql.AppendFormat("INSERT INTO [{0}$] (", dt.TableName); for (int i = 0; i < dt.Columns.Count; i++) { if (!string.IsNullOrEmpty(dt.Columns[i].Caption)) { sql.AppendFormat("[{0}],", dt.Columns[i].Caption); } } sql.Remove(sql.Length - 1, 1); sql.Append(") VALUES "); string head = sql.ToString(); for (int j = 0; j < dt.Rows.Count; j++) { sql = new StringBuilder(); sql.Append(head); sql.Append(" ("); for (int k = 0; k < dt.Columns.Count; k++) { if (!string.IsNullOrEmpty(dt.Columns[k].Caption)) { if (dt.Columns[k].DataType.Equals(typeof(String)) || dt.Columns[k].DataType.Equals(typeof(DateTime)) || dt.Columns[k].DataType.Equals(typeof(Char))) { sql.AppendFormat("'{0}'", dt.Rows[j][k].ToString()); } else { sql.AppendFormat("{0}", dt.Rows[j][k].ToString().Trim().Equals(string.Empty) ? 0 : Convert.ToDecimal(dt.Rows[j][k])); } sql.Append(","); } } sql.Remove(sql.Length - 1, 1); sql.Append(") "); cmd.CommandText = sql.ToString(); cmd.ExecuteNonQuery(); } } } catch (Exception ex) { throw ex; } finally { if (conn != null) { conn.Close(); conn.Dispose(); } } }
/*<summary> * 将DataTable中的数据导出到Excel中,并在服务器端AppData文件夹中生成xls文件 * </summary> * <param name="dt">要导出数据的DataTable</param> * <param name="head">题头数据</param> * <param name="absFileName">文件的绝对路径</param> * <returns></returns> */ public static void ExportToExcelDiagramfor9(System.Data.DataTable dt, string[] head, string absFileName) { //设置多少行为一个Sheet int RowsToDivideSheet = 65535; //计算Sheet数 int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1; GC.Collect(); string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径 string tempEFilePath = "\\TempFiles"; //EXCEL临时文件保存服务器物理存放路径 string tempEFileXPath = "/TempFiles"; //EXCEL临时文件保存服务器虚拟存放路径 Excel.Application excel = null; Excel._Workbook xBk = null; Excel._Worksheet xSt = null; Excel.Workbooks workbooks; //工作簿集合 Excel.Sheets sheets; //SHEET页集合 #region //读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称 excelTemplateDPath = System.Web.HttpContext.Current.Server.MapPath("~/moban"); tempEFileXPath = "~/moban" + tempEFileXPath; tempEFilePath = excelTemplateDPath + tempEFilePath; excelTemplateDPath += "\\TaskTemplet9.xls"; //验证EXCEL临时文件夹是否存在 if (!File.Exists(tempEFilePath)) { Directory.CreateDirectory(tempEFilePath); } #endregion try { #region //启动excel进程并加载模板 //启动EXCEL进程 excel = new Excel.Application(); excel.Visible = false; excel.UserControl = true; excel.DisplayAlerts = false; //加载读取模板 workbooks = excel.Workbooks; xBk = workbooks.Add(excelTemplateDPath); sheets = xBk.Worksheets; xSt = (Excel._Worksheet)sheets.get_Item(1); #endregion //循环中要使用的变量 int dvRowStart; int dvRowEnd; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //计算起始行 dvRowStart = sheetIndex * RowsToDivideSheet; dvRowEnd = dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dt.Rows.Count - 1) { dvRowEnd = dt.Rows.Count - 1; } //创建一个Sheet if (null == xSt) { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = "Excel"; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } //题头导出 int rowCount = head.Length; int colCount = 1; object[,] dataArray = new object[colCount, rowCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[j, i] = head[i]; } } xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10; //数据导出 System.Data.DataTable SheetTable = new System.Data.DataTable(); foreach (DataColumn dc in dt.Columns) { DataColumn newdc = new DataColumn(); newdc.ColumnName = dc.ColumnName; newdc.DataType = dc.DataType; SheetTable.Columns.Add(newdc); } for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++) { SheetTable.ImportRow(dt.Rows[drvIndex]); } //保存数据 rowCount = SheetTable.Rows.Count; colCount = SheetTable.Columns.Count; object[,] dataArray1 = new object[rowCount, colCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray1[i, j] = SheetTable.Rows[i][j]; } } xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10; //单元格边框 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐 xSt.Columns.AutoFit(); //自适应宽度 //设置图标的标题 string Charttitle = dt.Rows[0][0].ToString(); } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true; //注意一定要加上这句 object objOpt = System.Reflection.Missing.Value; excel.Visible = false; xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } catch (Exception e) { //throw (e); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } }