C#后台创建Excel文件的那点事儿

来源：互联网发布：企宣美工招聘编辑：程序博客网时间：2024/06/04 17:52

使用C#在应用中生成excel文档，甚至再提供直接下载功能，算是一个比较常用的应用，这里记录下自己遇到的几种方法，还有一些需要注意的地方：

a) 首先定义一个数据类：

public class DataContent { public string Name { set; get; } public string Address { set; get; } public string Phone { set; get; } public string GetContentByIndex(int index) { string Value = string.Empty; switch (index) { case 1: Value = Name; break; case 2: Value = Address; break; case 3: Value = Phone; break; default: Value = ""; break; } return Value; } }

b) 最简方法：直接通过拼凑HTML字符串来生成excel文件，优点是：代码十分方便，无需任何的office接口扩展即可工作；不足为：覆盖的单元格有限，虽然可以通过中间方法来设置，但仍存在打开时提示格式不正确的问题，随不影响正常浏览及编辑，但对于一些需要通过代码操作excel文件的应用，可能会遇到问题，比如通过OleDB来读取内容的时候等。

Public void GetExcelDataToClient(){List<DataContent> arrDatas = new List<DataContent>() { new DataContent() { Address="aaa", Name="aaa", Phone="aaa" }, new DataContent() { Address="bbb", Phone="bbb", Name="bbb" }, new DataContent() { Name="ccc", Phone="ccc", Address="ccc"} }; StringBuilder sb = new StringBuilder("<HTML xmlns:o=/"urn:schemas-microsoft-com:office:office/" xmlns:x=/"urn:schemas-microsoft-com:office:excel/" xmlns=/"http://www.w3.org/TR/REC-html40/"><HEAD><META HTTP-EQUIV=/"Content-Type/" content=/"text/html; charset=gb_2312-80/"><TITLE></TITLE><mce:style></mce:style><style mce_bogus="1">td{mso-number-format:/"//@/";}</style></HEAD><BODY><TABLE border=/"1/">", 500); // title columns. sb.Append(string.Format("<TR><TD><B>Event_CourseId<B/></TD><TD><B>UserId</B></TD><TD><B>Attended</B></TD>{0}</TR>", GetExCells(30))); // content columns. foreach (DataContent data in arrDatas) sb.Append(string.Format("<TR><TD>{0}</TD><TD>{1}</TD><TD>{2}</TD>{3}</TR>", data.Name, data.Address, data.Phone, GetExCells(30))); // add extension rows. sb.Append(GetExRows(50, 33)); // last part. sb.Append("</TABLE></BODY></HTML>"); // response the result as a excel file. Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment; filename=DataList.xls;filetype=excel"); Response.ContentEncoding = Encoding.GetEncoding("utf-8"); Response.Write(sb.ToString()); Response.Flush(); Response.End();} // add the extension cells. private string GetExCells(int num) { StringBuilder sb = new StringBuilder(200); for (int i = 0; i < num; i++) sb.Append("<TD></TD>"); return sb.ToString(); } // add the extension rows. private string GetExRows(int rowNum, int colNum) { StringBuilder sb = new StringBuilder(200); for (int i = 0; i < rowNum; i++) sb.Append(string.Format("<TR>{0}</TR>", GetExCells(colNum))); return sb.ToString();}

c) 通用接口方法：主要通过调用Office提供的接口组件Microsoft.Office.Interop.Excel，来生成excel文件，其特点是可以消除内容单元格覆盖不全的问题，而且也可以消除打开提示格式异常的问题，但针对通过OleDB来操作文件的情况，还必须按照一种比较严格的方式进行编写才成，否则就会出现操作过程中格式异常的问题，下面为标准的写法：

private void Test4() { string filePath = @"C:/Test.xls"; List<DataContent> arrDatas = new List<DataContent>() { new DataContent() { Address="aaa", Name="aaa", Phone="aaa" }, new DataContent() { Address="bbb", Phone="bbb", Name="bbb" }, new DataContent() { Name="ccc", Phone="ccc", Address="ccc"} }; object objOpt = System.Reflection.Missing.Value; Excel.Application objExcel = null; Excel.Workbooks objBooks = null; Excel.Workbook objBook = null; try { try { objExcel = new Excel.Application(); } catch (Exception ex) { Console.Write(ex.Message); } objBooks = (Excel.Workbooks)objExcel.Workbooks; objBook = (Excel.Workbook)(objBooks.Add(objOpt)); // Add data to cells of the first worksheet in the new workbook. Excel.Sheets objSheets = (Excel.Sheets)objBook.Worksheets; Excel.Worksheet objSheet = (Excel.Worksheet)(objSheets.get_Item(1)); objSheet.Name = "DataList"; objSheet.Cells[1, 1] = "Name"; objSheet.Cells[1, 2] = "Address"; objSheet.Cells[1, 3] = "Phone"; Excel.Range objRange = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 3]); //objRange.Font.Bold = true; objRange.Font.Size = 18; objRange.Interior.ColorIndex = 37; for (int i = 1; i < 4; i++) { objRange = objSheet.get_Range(objSheet.Cells[2, i], objSheet.Cells[2 + arrDatas.Count, i]); objRange.NumberFormatLocal = "@"; for (int j = 0; j < arrDatas.Count; j++) objSheet.Cells[2 + j, i] = arrDatas[j].GetContentByIndex(i); } objSheet.Columns.AutoFit(); if (System.IO.File.Exists(filePath)) System.IO.File.Delete(filePath); // Save the file. objBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, objOpt, objOpt, objOpt, objOpt, Excel.XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt); objBook.Close(false, objOpt, objOpt); objExcel.Quit(); objExcel = null; // Response the file to client. string downloadFileName = string.Format("{0:yyyyMMdd}", DateTime.Now) + "-" + this.Page.User.Identity.Name + "-DataList.xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment; filename=" + downloadFileName + ";filetype=excel"); Response.ContentEncoding = Encoding.GetEncoding("utf-8"); Response.WriteFile(filePath); Response.Flush(); if (System.IO.File.Exists(filePath)) System.IO.File.Delete(filePath); Response.End(); } catch (Exception ex) { if (objExcel != null) { try { foreach (Excel.Workbook wb in objExcel.Workbooks) { if (wb != null) { try { wb.Saved = true; } catch { } } } objExcel.Workbooks.Close(); objExcel.Quit(); objExcel = null; } catch { } } } } }