关于.net 对excel操作的方法
asp.net打印文件用的最多的一般2种,word和excel,今天在这里整洁一下关于打印excel的几种方式及优缺点
第一种:直接打印html代码,然后将输出类型伪装成excel文件(excel是可以识别xml和html一些代码的)
代码示例子:
/// <summary> /// 直接导出html并且强命名为excel格式文件 /// 优点:导出速度快,代码易读 适合快速打印、查看 /// 缺点:由于并非是真正意义上的excel文件 所有缺少excel的文件头,不适合进行一些导入等之类的运用。一些html控件的标签需要进行处理 /// 推荐地方:用于一些不需要对excel进行再处理的地方 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void BtnHtml_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("HtmlExcel.xls", Encoding.UTF8).ToString()); Response.ContentType = "application/ms-excel"; PageInfo<VW_CLN_LendAfter_CustDetail> PageToPtint = ViewEntitiy.VW_CLN_LendAfter_CustDetail.Query(0, 15, "select * from VW_CLN_LendAfter_CustDetail where 1=1 ", ToList); DataSoucre = (DataSet)PageToPtint.DataList; DataTable dt = DataSoucre.Tables[0]; foreach(string key in TotableDC.Keys) { if (dt.Columns.Contains(key)) { dt.Columns[key].ColumnName = TotableDC[key]; } } StringBuilder strexcel = new StringBuilder(); strexcel.Append(@"<table><tr>"); //导出标题 foreach(DataColumn dc in dt.Columns) { if (TotableDC.Values.Contains(dc.ColumnName)) { strexcel.Append(@"<th>" + dc.ColumnName + "</th>"); } } strexcel.Append(@"</tr>"); //导出列值 foreach (System.Data.DataRow dr in dt.Rows) { strexcel.Append(@"<tr>"); foreach(System.Data.DataColumn dc in dt.Columns) { if (TotableDC.Values.Contains(dc.ColumnName)) { strexcel.Append(@"<td>" + dr[dc.ColumnName] + @"</td>"); } } strexcel.Append(@"</tr>"); } strexcel.Append(@"<table>"); Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=utf-8\"/>" + strexcel); Response.End(); }
第二种:使用Appliction导出excel 这里说的Applcation是由微软提供的OFFICE DOM组件。这种优点与缺点都非常明显
代码示例:
/// <summary> /// 使用Appliction导出excel /// 优点:程序可控制性强,因为使用微软本身提供的Excel打开程序 /// 缺点:需要打开excel程序有安全隐患,IIS和程序的本身需要很高权限,容易为关闭平台导致报错,多人同时使用时容易发生冲突,服务器压力负荷较大 /// 推荐地方:在需要对excel需要进行深度开发时使用,其他情况一概不建议使用 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void BtnApplication_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; Response.Charset = "utf-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("HtmlExcel.xlsx", Encoding.UTF8).ToString()); Response.ContentType = "application/ms-excel"; System.IO.FileInfo FileInfo = new System.IO.FileInfo(Server.MapPath("b报表.XLS")); if (FileInfo.Exists) { FileInfo.Delete(); } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); ; Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); ; Microsoft.Office.Interop.Excel.Worksheet worksheet; Microsoft.Office.Interop.Excel.Range ExcelRange; try { worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; PageInfo<VW_CLN_LendAfter_CustDetail> PageToPtint = ViewEntitiy.VW_CLN_LendAfter_CustDetail.Query(0, 15, "select * from VW_CLN_LendAfter_CustDetail where 1=1 ", ToList); DataSoucre = (DataSet)PageToPtint.DataList; DataTable dt = DataSoucre.Tables[0]; int intcolums = 0; foreach (string key in TotableDC.Keys) { if (dt.Columns.Contains(key)) { dt.Columns[key].ColumnName = TotableDC[key]; } } //导出标题 foreach (DataColumn dc in dt.Columns) { if (TotableDC.Values.Contains(dc.ColumnName)) { //计算Range名 这个与excel中行用A 列用1表示类似 如果超过Z便要使用其他方式计算 这里就不作计算了 string rangename = (Char)(intcolums + 65) + "1"; //worksheet.Range[rangename]如果带一个字符代表选择一个 如果是如worksheet.Range["A1","G5"]就相当于选择了成A1到G5之间的所有单元格 定义格式时很好用 ExcelRange = worksheet.Range[rangename]; ExcelRange.Value = dc.ColumnName; intcolums += 1; } } //导出列值 int introw = 2; foreach (System.Data.DataRow dr in dt.Rows) { intcolums = 0; foreach (System.Data.DataColumn dc in dt.Columns) { if (TotableDC.Values.Contains(dc.ColumnName)) { string rangename = (Char)(intcolums + 65) + introw.ToString(); ExcelRange = worksheet.Range[rangename]; ExcelRange.NumberFormat = "@"; ExcelRange.Value = dr[dc.ColumnName]; intcolums += 1; } } introw += 1; } workbook.Saved = true; workbook.SaveAs(Server.MapPath( "b报表.XLS")); } catch(Exception ex) { } finally { workbook.Close(true, Type.Missing, Type.Missing); workbook = null; xlApp.Quit(); xlApp = null; } Response.WriteFile(Server.MapPath( "b报表.XLS")); Response.End(); }
第三种:使用ADO.NET进行sql链接
/// <summary> /// 使用ADO.NET连接字符串进行导入' /// 优点:导入速度快,低层 /// 缺点:可编译性弱,语言生涩,操作性不强,列名必须放在第一行,自建格式必须使用其他方式创建列否则不能放在第一行 /// 推荐使用地方:仅推荐放在无标题并且有自定义模板的word导出,其他地方不推荐 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void BtnString_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; Response.Charset = "utf-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("HtmlExcel.xlsx", Encoding.UTF8).ToString()); Response.ContentType = "application/ms-excel"; PageInfo<VW_CLN_LendAfter_CustDetail> PageToPtint = ViewEntitiy.VW_CLN_LendAfter_CustDetail.Query(0, 15, "select * from VW_CLN_LendAfter_CustDetail where 1=1 ", ToList); DataSoucre = (DataSet)PageToPtint.DataList; DataSet DS=new DataSet(); DataTable dt = DataSoucre.Tables[0].Copy(); DS.Tables.Add(dt); foreach (string key in TotableDC.Keys) { if (dt.Columns.Contains(key)) { dt.Columns[key].ColumnName = TotableDC[key]; } } FileInfo ModelInfo = new System.IO.FileInfo(Server.MapPath("Model.xlsx")); FileInfo FileInfo = new System.IO.FileInfo(Server.MapPath(DateTime.Now.ToString("yyyyMMdd")+".xlsx")); if (FileInfo.Exists) { FileInfo.Delete(); } ModelInfo.CopyTo(Server.MapPath(DateTime.Now.ToString("yyyyMMdd") + ".xlsx")); DataSoucre.Tables[0].TableName = "Biao"; DSToExcel2007(Server.MapPath(DateTime.Now.ToString("yyyyMMdd") + ".xlsx"), DS, DataSoucre.Tables[0].TableName); Response.WriteFile(Server.MapPath((DateTime.Now.ToString("yyyyMMdd") + ".xlsx"))); Response.End(); } /// <summary> /// 导出数据到Excel /// </summary> /// <param name="Path">需要导入的Excel地址</param> /// <param name="oldds">需要导入的数据</param> /// <param name="TableName">表名</param> public static void DSToExcel2007(string Path, DataSet oldds, string TableName) { //Excel2007的连接字符串 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0;ReadOnly=False \""; //执行导入 ExcuteSQL(oldds, TableName, strConn); } /// <summary> /// 执行 /// </summary> /// <param name="oldds">需要导入的数据</param> /// <param name="TableName">表名</param> /// <param name="strCon">连接字符串</param> private static void ExcuteSQL(DataSet oldds, string TableName, string strCon) { //连接 OleDbConnection myConn = new OleDbConnection(strCon); string strCom = "select * from [" + TableName + "$]"; try { //string CreateTable = ""; //foreach (System.Data.DataColumn dc in ndt.Columns) //{ // CreateTable += "ALTER table [" + TableName + "] add [" + dc.ColumnName + "] Text "; // OleDbCommand cmd = new OleDbCommand(CreateTable, myConn); // cmd.ExecuteNonQuery(); //} ////CreateTable = CreateTable.Trim(','); ////CreateTable += ")"; ////OleDbCommand cmd = new OleDbCommand(CreateTable, myConn); //// cmd.ExecuteNonQuery(); //System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand); myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); System.Data.DataTable ndt = oldds.Tables[0].Clone(); string CreateTable = "create table [" + TableName + "]("; foreach (System.Data.DataColumn dc in ndt.Columns) { CreateTable += "["+dc.ColumnName+"] Text,"; } CreateTable = CreateTable.Trim(','); CreateTable += ")"; OleDbCommand cmd = new OleDbCommand(CreateTable, myConn); cmd.ExecuteNonQuery(); string DelteTable = " drop table [Sheet1]"; System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand); //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。 //获取insert语句中保留字符(起始位置) builder.QuotePrefix = "["; //获取insert语句中保留字符(结束位置) builder.QuoteSuffix = "]"; DataSet newds = new DataSet(); //获得表结构 //清空数据 //ndt.Rows.Clear(); ndt.TableName = TableName; newds.Tables.Add(ndt); for (int i = 0; i < oldds.Tables[0].Rows.Count; i++) { //在这里不能使用ImportRow方法将一行导入到news中, //因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。 //在使用ImportRow后newds内有值,但不能更新到Excel中因为所有导入行的DataRowState!=Added DataRow nrow = newds.Tables[0].NewRow(); for (int j = 0; j < oldds.Tables[0].Columns.Count; j++) { nrow[j] = oldds.Tables[0].Rows[i][j]; } newds.Tables[0].Rows.Add(nrow); } //DataTable dt = oldds.Tables[1]; //插入数据 myCommand.Update(newds, TableName); } finally { myConn.Close(); } }
第四种,采用第三方组件,第三方组件有很多种,我这边使用的是aspose.cell组件
代码示例:
/// <summary> /// 使用Aspose导出excel /// 优点:导出灵活 可以自由编辑格式,改变样式,公共方法比较全面,对安全性能方面没有过多限制,速度也挺快 /// 缺点:一些方法不容易让人看明白,版本限制性强,对.net框架有要求 /// 推荐地方:极力推荐在.net4.0版本以上的系统,不推荐使用在.net 3.5以下版本,3.5酌情使用 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void BtnAspose_Click(object sender, EventArgs e) { Aspose.Cells.Workbook Worbook = new Aspose.Cells.Workbook("b报表.XLS"); Worksheet sheet = Worbook.Worksheets[0]; Cells cell = sheet.Cells; PageInfo<VW_CLN_LendAfter_CustDetail> PageToPtint = ViewEntitiy.VW_CLN_LendAfter_CustDetail.Query(0, 15, "select * from VW_CLN_LendAfter_CustDetail where 1=1 ", ToList); DataSoucre = (DataSet)PageToPtint.DataList; DataTable dt = DataSoucre.Tables[0].Copy(); foreach (string key in TotableDC.Keys) { if (dt.Columns.Contains(key)) { dt.Columns[key].ColumnName = TotableDC[key]; } } //对于导出 Aspose.CELLS有自己定义好的方法 可以F12自己看看还有哪些快速定义导入的方法 //此处解释是将dt里面的数据全部从列0行0位置的单元导入到Word中,并且显示列名为TRUE cell.ImportDataTable(dt, true, 0, 0); //此时定义为我导出发送给客户的一些选项,默认是xls的配置 所有我想导出xlsx就需要重新定义下 XlsSaveOptions saveOptions = new XlsSaveOptions() ; saveOptions.SaveFormat = SaveFormat.Xlsx; // Worbook.Save(Response, SaveFormat.Xlsx, ContentDisposition.Attachment, new SaveOptions(){ SaveType = SaveType.OpenInExcel, SaveFormat=SaveFormat.Xlsx, ClearData=true, CachedFileFolder="AsposeExcel.xlsx"}); //这其中保存有很多种方案 本地保存Worbook.Save(@路径字符串) Worbook.Save(Response,"BaoBiao.xlsx", ContentDisposition.Attachment,saveOptions); //Aspose一些需要涉及到的地方 //第几行第几列的单元格获取cell[0,1] //第几行第几列的单元格赋值cell[0,1].Value="值" //获取和设置单元格格式cell[0,1].SetStyle( new Aspose.Cells.Style(){ VerticalAlignment=TextAlignmentType.Center} ); cell[0,1].GetStyle() } }