C#操作EXCEL的DLL(com方式) 带帮助 测试代码

必须的使用方法： 1. 实例化excelutil对象 2. 选择初始化方式，必须的 3. ........其他的处理......... 4. close() //ExcelOpHelper xlsOp = null; protected void Button2_Click(object sender, EventArgs e) { //此段代码为写成时的测试代码，可能与之后的版本不同，仅供参与 xlsOp = new ExcelOpHelper(); string templateFilePath = Server.MapPath("template\\month_report.xls"); if (!System.IO.File.Exists(templateFilePath)) { Response.Write("<script>alert('请检查模板文件是否存在!')</script>"); return; } Response.Clear(); Response.Buffer = true; Response.Charset = "gb2312"; //中文文件名必须用此方法处理,否则乱码 Response.AppendHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("财务月报表.xls", System.Text.Encoding.UTF8)); Response.ContentEncoding = System.Text.Encoding.Unicode; Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 int startRowIndex = 4;//操作区域开始行索引 int startColIndex = 1; object rowStartCell = null; xlsOp.InitExcelFrom(templateFilePath); xlsOp.SetWorkingSheet(1); xlsOp.NewWorkSheet("zzzzzzzzzz"); rowStartCell = xlsOp.GetCell(startRowIndex, startColIndex);//区域开始单元格对象 DataTable dt = getDate(); int lastRowIndex = xlsOp.Fill(rowStartCell, dt);//填充数据 xlsOp.SetCellText(xlsOp.GetCell(lastRowIndex, 2), "合计"); xlsOp.SetBold(xlsOp.GetCell(lastRowIndex, 2), xlsOp.GetCell(lastRowIndex, 12)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 3), String.Format("=sum(c4:c{0})", lastRowIndex - 1)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 5), String.Format("=sum(e4:e{0})", lastRowIndex - 1)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 6), String.Format("=sum(f4:f{0})", lastRowIndex - 1)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 7), String.Format("=sum(g4:g{0})", lastRowIndex - 1)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 8), String.Format("=sum(h4:h{0})", lastRowIndex - 1)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 9), String.Format("=sum(i4:i{0})", lastRowIndex - 1)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 10), String.Format("=sum(j4:j{0})", lastRowIndex - 1)); xlsOp.SetFormula(xlsOp.GetCell(lastRowIndex, 11), String.Format("=sum(k4:k{0})", lastRowIndex - 1)); xlsOp.SetDisplayFormat(xlsOp.GetCell(startRowIndex, 4), xlsOp.GetCell(lastRowIndex, 5), "0.00"); xlsOp.SetDisplayFormat(xlsOp.GetCell(startRowIndex, 6), xlsOp.GetCell(lastRowIndex, 6), "0.00"); xlsOp.SetDisplayFormat(xlsOp.GetCell(startRowIndex, 7), xlsOp.GetCell(lastRowIndex, 7), "0.00"); xlsOp.SetDisplayFormat(xlsOp.GetCell(startRowIndex, 11), xlsOp.GetCell(lastRowIndex, 11), "0.00"); xlsOp.SetFontName(xlsOp.GetCell(startRowIndex, 1), xlsOp.GetCell(lastRowIndex, 12), "仿宋_GB2312"); xlsOp.SetRangeBorder(xlsOp.GetCell(startRowIndex, 1), xlsOp.GetCell(lastRowIndex, 12)); String fileName = Server.MapPath("tempxls\\" + DateTime.Now.ToFileTime() + ".xls"); xlsOp.SetAutoWrapText(xlsOp.GetCell(4, 2), xlsOp.GetCell(lastRowIndex, 2)); xlsOp.AutoWrapText(xlsOp.GetCell(3, 8)); //自动筛选 xlsOp.SetAutoFilter(xlsOp.GetCell(3, 3), xlsOp.GetCell(lastRowIndex, 3)); xlsOp.SetDisplayFormat(xlsOp.GetCell(6, 12), "yyyy-MM-dd hh:mm:ss"); xlsOp.SetDisplayFormat(xlsOp.GetCell(7, 12), "yyyy年MM月dd日hh时mm分ss秒"); xlsOp.SetAlign(xlsOp.GetCell(3, 3), Align.Center, VAlign.Middle); xlsOp.SetWorkingSheet(2); xlsOp.Fill(xlsOp.GetCell(1, 1), dt);//填充数据 xlsOp.AddHyperLink(xlsOp.GetCell(1, 1), "http://www.163.com", "我操", "点我就去网易了"); xlsOp.SetBgColor(xlsOp.GetCell(1, 1), System.Drawing.Color.Yellow); xlsOp.SetWorkingSheet(1); CellStyle style = new CellStyle(); style.AlignType = Align.Center; style.ValignType = VAlign.Middle; style.RowHeight = 40; style.ColumnWidth = 30; style.BgColor = System.Drawing.Color.Yellow; style.Color = System.Drawing.Color.Red; style.FontSize = 20; style.FontName = "隶书"; xlsOp.SetStyle(xlsOp.GetCell(11, 11), xlsOp.GetCell(1, 1), style); xlsOp.SaveAs("c:\\a.xml"); //xlsOp.Save(); xlsOp.Close(); //xlsOp.Dispose();//必须先释放,否则后面打不开文件 System.IO.FileStream z = new System.IO.FileStream(templateFilePath, System.IO.FileMode.Open); System.IO.BinaryReader s = new System.IO.BinaryReader(z); byte[] sss = s.ReadBytes((int)z.Length); Response.OutputStream.Write(sss, 0, sss.Length); z.Close(); s.Close(); if (System.IO.File.Exists(fileName)) { System.IO.File.Delete(fileName); } Response.Flush(); Response.End(); } private DataTable getDate() { DataTable dt = new DataTable("data"); dt.Columns.Add(new DataColumn("index", typeof(int))); dt.Columns.Add(new DataColumn("tapeName", typeof(string))); dt.Columns.Add(new DataColumn("lamount", typeof(int))); dt.Columns.Add(new DataColumn("uprice", typeof(int))); dt.Columns.Add(new DataColumn("lMoney", typeof(int))); dt.Columns.Add(new DataColumn("inaccount", typeof(string))); dt.Columns.Add(new DataColumn("inMoney", typeof(string))); dt.Columns.Add(new DataColumn("outAmount", typeof(string))); dt.Columns.Add(new DataColumn("outMoney", typeof(string))); dt.Columns.Add(new DataColumn("amount", typeof(string))); dt.Columns.Add(new DataColumn("money", typeof(string))); dt.Columns.Add(new DataColumn("remark", typeof(DateTime))); for (int i = 0; i < 20; i++) { DataRow dr = dt.NewRow(); dr["index"] = i + 1; dr["tapeName"] = "BCT-32MX磁带"; dr["lamount"] = i * 5 + 2; dr["uprice"] = i * 2.213 + 1.5; dr["lMoney"] = 3000; dr["inaccount"] = (i*20)/3; dr["inMoney"] = (i + 1.2) * 300; dr["outAmount"] = 32; dr["outMoney"] = (i + 2.0) * 400; dr["amount"] = i+10; dr["money"] = i * 210; dr["remark"] = DateTime.Now;// "remark " + i.ToString(); dt.Rows.Add(dr); } return dt; }