根据服务器上的Excel模版导出Excel
命名空间：
Using
using System; using System.Collections.Generic; using System.Linq; using System.Web; using Microsoft.Office.Tools.Excel; using Microsoft.Office.Interop.Excel; using System.Text; using System.IO; using System.Xml; using System.Globalization; using System.Data; using System.Collections; using System.Collections.Specialized; using System.Data.Common; using System.Data.SqlClient; using Microsoft.Practices.EnterpriseLibrary.Logging; using System.Data.OleDb; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Net.Mime; using System.Reflection; using System.Diagnostics; using Microsoft.Win32; using System.Runtime.InteropServices;
class ExcelOperate:
需要调用的函数
/// <summary> /// ExcelOperate 的摘要说明。Excel操作函数 /// </summary> class ExcelOperate { private object mValue = System.Reflection.Missing.Value; public ExcelOperate() { // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 合并单元格 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void Merge(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue); } /// <summary> /// 设置连续区域的字体大小 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="strStartCell">开始单元格</param> /// <param name="strEndCell">结束单元格</param> /// <param name="intFontSize">字体大小</param> public void SetFontSize(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize) { CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString(); } /// <summary> /// 横向打印 /// </summary> /// <param name="CurSheet"></param> public void xlLandscape(Microsoft.Office.Interop.Excel._Worksheet CurSheet) { CurSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; } /// <summary> /// 纵向打印 /// </summary> /// <param name="CurSheet"></param> public void xlPortrait(Microsoft.Office.Interop.Excel._Worksheet CurSheet) { CurSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; } /// <summary> /// 在指定单元格插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="Cell">单元格 如Cells[1,1]</param> /// <param name="objValue">文本、数字等值</param> public void WriteCell(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, object objValue) { CurSheet.get_Range(objCell, mValue).Value2 = objValue; } /// <summary> /// 在指定Range中插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="StartCell">开始单元格</param> /// <param name="EndCell">结束单元格</param> /// <param name="objValue">文本、数字等值</param> public void WriteRange(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue) { CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue; } /// <summary> /// 合并单元格，并在合并后的单元格中插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="objValue">文本、数字等值</param> public void WriteAfterMerge(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue) { CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue); CurSheet.get_Range(objStartCell, mValue).Value2 = objValue; } /// <summary> /// 为单元格设置公式 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objCell">单元格</param> /// <param name="strFormula">公式</param> public void SetFormula(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strFormula) { CurSheet.get_Range(objCell, mValue).Formula = strFormula; } /// <summary> /// 单元格自动换行 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void AutoWrapText(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).WrapText = true; } /// <summary> /// 设置整个连续区域的字体颜色 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="clrColor">颜色</param> //public void SetColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor) //{ // CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor); //} /// <summary> /// 设置整个连续区域的单元格背景色 /// </summary> /// <param name="CurSheet"></param> /// <param name="objStartCell"></param> /// <param name="objEndCell"></param> /// <param name="clrColor"></param> //public void SetBgColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor) //{ // CurSheet.get_Range(objStartCell, objEndCell).Interior.Color = System.Drawing.ColorTranslator.ToOle(clrColor); //} /// <summary> /// 设置连续区域的字体名称 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param> public void SetFontName(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname) { CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname; } /// <summary> /// 设置连续区域的字体为黑体 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetBold(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { //CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true; CurSheet.get_Range(objStartCell, objEndCell).Font.Size = 12; } /// <summary> /// 设置连续区域的边框：上下左右都为黑色连续边框 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /* public void SetBorderAll(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; } */ /// <summary> /// 设置连续区域水平居中 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetHAlignCenter(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } /// <summary> /// 设置连续区域水平居左 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetHAlignLeft(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } /// <summary> /// 设置连续区域水平居右 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetHAlignRight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } /// <summary> /// 设置连续区域的显示格式 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="strNF">如"#,##0.00"的显示格式</param> public void SetNumberFormat(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string strNF) { CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF; } public void border(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object aa, object bb) { CurSheet.get_Range(aa, bb).Borders.LineStyle = 1; } /// <summary> /// 设置列宽 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="strColID">列标识，如A代表第一列</param> /// <param name="dblWidth">宽度</param> public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, string strColID, double dblWidth) { ((Microsoft.Office.Interop.Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[] { (strColID + ":" + strColID).ToString() })).ColumnWidth = dblWidth; } /// <summary> /// 设置列宽 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="dblWidth">宽度</param> public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblWidth) { CurSheet.get_Range(objStartCell, objEndCell).ColumnWidth = dblWidth; } /// <summary> /// 设置行高 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="dblHeight">行高</param> public void SetRowHeight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblHeight) { CurSheet.get_Range(objStartCell, objEndCell).RowHeight = dblHeight; } /// <summary> /// 为单元格添加超级链接 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objCell">单元格</param> /// <param name="strAddress">链接地址</param> /// <param name="strTip">屏幕提示</param> /// <param name="strText">链接文本</param> public void AddHyperLink(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strAddress, string strTip, string strText) { CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell), strAddress, mValue, strTip, strText); } /// <summary> /// 另存为xls文件 /// </summary> /// <param name="CurBook">Workbook</param> /// <param name="strFilePath">文件路径</param> public void Save(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath) { CurBook.SaveCopyAs(strFilePath); } /// <summary> /// 保存文件 /// </summary> /// <param name="CurBook">Workbook</param> /// <param name="strFilePath">文件路径</param> public void SaveAs(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath) { CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue); } /// <summary> /// 另存为html文件 /// </summary> /// <param name="CurBook">Workbook</param> /// <param name="strFilePath">文件路径</param> public void SaveHtml(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath) { CurBook.SaveAs(strFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue); } /// <summary> /// 释放内存 /// </summary> public void Dispose(Microsoft.Office.Interop.Excel._Worksheet CurSheet, Microsoft.Office.Interop.Excel._Workbook CurBook, Microsoft.Office.Interop.Excel._Application CurExcel, out string efb) { int i = 0; efb = i.ToString(); try { efb = i.ToString(); i = 1; System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet); CurSheet = null; i = 2; efb = i.ToString(); CurBook.Close(false, mValue, mValue); i = 3; efb = i.ToString(); System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook); CurBook = null; i = 4; efb = i.ToString(); CurExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel); CurExcel = null; i = 5; efb = i.ToString(); GC.Collect(); i = 6; efb = i.ToString(); GC.WaitForPendingFinalizers(); efb = i.ToString(); } catch (System.Exception ex) { HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex); i = 7; efb = i.ToString(); } finally { i = 8; efb = i.ToString(); foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel")) { //if (pro.StartTime < DateTime.Now) i++; efb = "b" + i.ToString(); pro.Kill(); efb = "e" + i.ToString(); } } efb = "elop" + i.ToString(); System.GC.SuppressFinalize(this); efb = "ov" + i.ToString(); } }
导出Excel方法：
View Code
public int GetTempExcele(int step, string info, string fb) { int result = 0; fb = ""; //建立一个Excel.Application的新进程 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); result = 1; if (app == null) { return result; } app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; result = 2; //if (ExistsRegedit07()) if (true) { string efb = string.Empty; try { string syspath = string.Empty; switch (step) { case 1://服务器上的Excel模版存储路径 syspath = System.Web.HttpContext.Current.Server.MapPath("\\inc\\Export\\kaoqin_tmp.xlsx"); break; default: break; } _Workbook workbook = workbooks.Add(syspath); result = 3; // _Workbook workbook = workbooks.Add(Missing.Value); //不用模板 Sheets sheets = workbook.Worksheets; result = 4; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //这里表示模板只有一个sheet表 _Worksheet worksheet2 = (_Worksheet)sheets.get_Item(2); result = 5; if (worksheet == null) { return result; } result = 7; switch (step) { case 1: //一览表写入 for (int i = 0; i < grvMonthInfo.Rows.Count; i++) { LinkButton username = (LinkButton)grvMonthInfo.Rows[i].FindControl("linkuser"); worksheet.Cells[i + 5, 2] = username.Text; worksheet.Cells[i + 5, 3] = grvMonthInfo.Rows[i].Cells[1].Text; } //员工详细信息写入 for (int i = 0; i < grvMonthInfo.Rows.Count; i++) { LinkButton UserId = (LinkButton)grvMonthInfo.Rows[i].FindControl("linkUserId"); OAInfo.Userid = int.Parse(UserId.Text); OAInfo.Time = drpdwnlstyear.SelectedValue + "-" + drpdwnlstmonth.SelectedValue; DataSet data = OAl.QueryPersonSign(OAInfo); _Worksheet tempSheet = (_Worksheet)sheets[2 + i]; //worksheet2.Copy(Type.Missing, sheets[2 + i]);//i=0,将第二张表拷贝一份出来 //_Worksheet tempSheet = (_Worksheet)sheets[3 + i];//第三张表 for (int j = 0; j < data.Tables[0].Rows.Count; j++) { tempSheet.Cells[j + 2, 1] = j + 1; tempSheet.Cells[j + 2, 2] = data.Tables[0].Rows[j][1].ToString(); tempSheet.Cells[j + 2, 3] = data.Tables[0].Rows[j][2].ToString(); DateTime dt = new DateTime(); if (data.Tables[0].Rows[j][3].ToString() != "") { dt = DateTime.Parse(data.Tables[0].Rows[j][3].ToString()); tempSheet.Cells[j + 2, 4] = dt.ToString("HH:mm"); } else { tempSheet.Cells[j + 2, 4] = data.Tables[0].Rows[j][3].ToString(); } if (data.Tables[0].Rows[j][4].ToString() != "") { dt = DateTime.Parse(data.Tables[0].Rows[j][4].ToString()); tempSheet.Cells[j + 2, 5] = dt.ToString("HH:mm"); } else { tempSheet.Cells[j + 2, 5] = data.Tables[0].Rows[j][4].ToString(); } if (data.Tables[0].Rows[j][5].ToString() != "") { int seconds = int.Parse(data.Tables[0].Rows[j][5].ToString()); string time = string.Format("{00:00}:{01:00}", seconds / 3600, seconds % 3600 / 60); tempSheet.Cells[j + 2, 6] = time; } else { tempSheet.Cells[j + 2, 6] = data.Tables[0].Rows[j][5].ToString(); } if (data.Tables[0].Rows[j][6].ToString()!="") { int seconds = int.Parse(data.Tables[0].Rows[j][6].ToString()); string time = string.Format("{00:00}:{01:00}", seconds / 3600, seconds % 3600 / 60); tempSheet.Cells[j + 2, 7] = time; } else { tempSheet.Cells[j + 2, 7] = data.Tables[0].Rows[j][6].ToString(); } } } app.DisplayAlerts = true; break; default: break; } result = 8; string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; string dname = string.Empty; switch (step) { case 1: dname = "kaoqin_311_" + drpdwnlstyear.SelectedItem.Text+drpdwnlstmonth.SelectedItem.Text+ ".xlsx"; break; case 2: dname = "jiaowu_baobiao.xlsx"; break; case 3: dname = "caiwu_baobiao.xlsx"; break; default: break; } filename = HttpContext.Current.Server.MapPath(filename); result = 9; workbook.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//save_path为要保存到的文件路径 result = 10; ExcelOperate excelOperate = new ExcelOperate(); result = 11; excelOperate.Dispose(worksheet, workbook, app, out efb);//生成好EXCEL文件后关闭Excel进程 result = 15; FileInfo fi = new FileInfo(filename);//excelFile为文件在服务器上的地址 HttpResponse contextResponse = HttpContext.Current.Response; contextResponse.Clear(); contextResponse.Buffer = true; contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 //contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", filename)); //定义输出文件和文件名 contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", dname)); contextResponse.AppendHeader("Content-Length", fi.Length.ToString()); contextResponse.ContentEncoding = Encoding.Default; contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 contextResponse.WriteFile(fi.FullName); //contextResponse.WriteFile(string.Format("tp_extra_step_{0}.xls",step)); result = 16; contextResponse.Flush(); result = 17; fi.Delete(); result = 18; contextResponse.End(); result = 19; File.Delete(filename); } catch (Exception ex) { string s = ex.Message; fb = ex.Message + "efb is:" + efb; } return result; } return result; }
调用Excel
protected void btnExcel_Click(object sender, EventArgs e) { int number = GetTempExcele(1,"",""); }
