Open XML 是从office 2007 开始被支持的, 用Open xml可以不用Office dcom创建标准office 文档, Office Dcom进程释放不掉, 实在太烦人了, 而且资源消耗大. 用Open xml可以直接生成office 文档, 不过现在文档资料比较少, 且方法不是很可取, 包括msdn上的一些文档, 创建office 文档都是用拼字符串的方法来做的, 个人感觉不符合C#编码习惯, 不过也情有可原, 这文档大多是在open xml sdk1.0 年代写的, 先Open xml SDK2.0出来了, 经过一阵专研, 终于写出了一个excel 文件, 不过可惜, boss 考虑到风险问题, 让我继续要dcom创建, 因此仅仅研究到创建excel 文件, 并往cell里写text. 不过这种方法对于asp.net 动态生成 excel 下载, 还是很有用途的, boss 要求的下载的excel 要求带chart的, 就是根据数据生成chart在下载的excel文件中, 因此那种直接response girdview的方法不能满足要求.
闲话少叙, 看代码.
用open xml 需要首先安装 open xml sdk, 并且在.net 3.0环境, 因为它需要用LINQ.添加DocumentFormat.OpenXml 引用使用名称空间:using System.IO.Packaging; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;
// 创建流, 如果用在asp.net下载, 可以用memorystream
FileStream fs = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.ReadWrite);
//创建spreadsheetDocument
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fs, SpreadsheetDocumentType.Workbook)) {
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//ShareString 不知道是做什么用, 没有找到相关文档介绍 SharedStringTablePart sharestringTablePart = workbookPart.AddNewPart<SharedStringTablePart>(); sharestringTablePart.SharedStringTable = new SharedStringTable(); int i = 0; foreach (SharedStringItem item in sharestringTablePart.SharedStringTable.Elements<SharedStringItem>()) { string str = item.InnerText; i++; } sharestringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text("teasfsdfasdfxt"))); sharestringTablePart.SharedStringTable.Save();
//SharedStringTable好像不起什么作用, 没有好像还不行
//下边的代码是创建 sheet
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet. uint sheetId = 1;
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet);
//下边的代码是往sheet里写东西
SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild<SheetData>(); Row row = new Row(){RowIndex = 2}; sheetData.Append(row); Cell newCell = new Cell() { CellReference = "B2" }; newCell.DataType = new EnumValue<CellValues>(CellValues.String); row.InsertAt<Cell>(newCell, 0); newCell.CellValue = new CellValue("1");
newCell.CellValue.Text = "testt"; //这个保存很重要 newWorksheetPart.Worksheet.Save(); workbookPart.Workbook.Save(); spreadSheet.Close(); fs.Close(); }
在创建的过程中一点错误就会导致创建的excel不能被打开. 另外有个外国公司已经封装了这个open xml, 很好用,很easy, 不过要钱. 网址: http://www.spreadsheetgear.com/
顺便发上我写用 exce Dcom 生成 excel
Code 1using System; 2using System.Data; 3using System.Collections.Generic; 4using System.Configuration; 5using System.Web; 6using System.Web.Security; 7using System.Web.UI; 8using System.Web.UI.HtmlControls; 9using System.Web.UI.WebControls; 10using System.Web.UI.WebControls.WebParts; 11 12using System.Drawing; 13using Excel = Microsoft.Office.Interop.Excel; 14using System.Windows.Forms; 15namespace ExportToExcel 16{ 17 public class ExcelHelper : IDisposable 18 { 19 Excel.Application excel; 20 Excel.Workbooks oBooks; 21 Excel.Workbook workBook; 22 Excel.Worksheet worksheet; 23 List<object> dcoms = new List<object>(); 24 public Excel.Worksheet CurrentWorksheet { get { return worksheet; } } 25 public ExcelHelper() 26 { 27 28 excel = new Excel.Application(); 29 oBooks = excel.Workbooks; 30 workBook = oBooks.Add(System.Reflection.Missing.Value); 31 workBook.Application.Visible = false; 32 //if (workBook.Worksheets.Count > 0) 33 // workBook.Worksheets[1] as Excel.Worksheet; 34 worksheet = (Excel.Worksheet)excel.ActiveSheet; 35 36 } 37 38 public void SaveAs(string fileName) 39 { 40 workBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive 41 , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 42 43 // excel.Save(fileName); 44 } 45 public void Dispose() 46 { 47 foreach (object obj in dcoms) 48 { 49 if(obj != null) 50 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj); 51 } 52 dcoms.Clear(); 53 if (worksheet != null) 54 { 55 System.Runtime.InteropServices.Marshal.FinalReleaseComObject((object)worksheet); 56 worksheet = null; 57 } 58 if (workBook != null) 59 { 60 workBook.Close(false, Type.Missing, Type.Missing); 61 62 System.Runtime.InteropServices.Marshal.FinalReleaseComObject((object)workBook); 63 64 workBook = null; 65 } 66 if (oBooks != null) 67 { 68 oBooks.Close(); 69 System.Runtime.InteropServices.Marshal.FinalReleaseComObject((object)oBooks); 70 oBooks = null; 71 } 72 if (excel != null) 73 { 74 75 76 excel.Quit(); 77 83 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel); 84 excel = null; 85 86 } 87 88 GC.Collect(0); 89 } 90 91 public void RangeTextAlign(string startCell, string endCell, Excel.XlHAlign hAlign, Excel.XlVAlign vAlign) 92 { 93 Excel.Range range = worksheet.get_Range(startCell, endCell); 94 range.HorizontalAlignment = hAlign; 95 range.VerticalAlignment = vAlign; 96 97 } 98 99 public void RangeMerge(string startCell, string endCell, bool merge)100 {101 Excel.Range range = worksheet.get_Range(startCell, endCell);102103 if (merge)104 range.Merge(true);105 else106 range.UnMerge();107 }108 public void RangeWrapText(string startCell, string endCell, bool wrapText)109 {110 Excel.Range range = worksheet.get_Range(startCell, endCell);111112 range.WrapText = wrapText;113 }114115 public void WriteTextToRange(string startCell, string endCell, string text, bool merge, bool wrapText, Excel.XlHAlign hAlign, Excel.XlVAlign vAlign)116 {117 118 Excel.Range range = worksheet.get_Range(startCell, endCell);119 //int indexX, indexY;120 //ConvertCellToIndex(startCell, out indexX, out indexY);121 range.Cells[1, 1] = text;122 123 range.HorizontalAlignment = hAlign;124 range.VerticalAlignment = vAlign;125 if (merge)126 range.Merge(true);127 else128 range.UnMerge();129 range.WrapText = wrapText;130 }131 public void ConvertCellToIndex(string cellName, out int indexX, out int indexY)132 {133 indexX = cellName.ToUpper()[0]- 'A' +1;134 indexY = cellName.ToUpper()[1] - '0';135 }136 public string ConvertIndexToCellx(int indexX, int indexY)137 {138 char A = Convert.ToChar('A' + indexX - 1);139 char B = Convert.ToChar('1' + indexY -1);140 return A.ToString() + B;141 }142143 public void WriteTableToExcelSheet(DataTable dt, bool writeHeader, int startX, int startY)144 {145 int i = 0, j = 0;146 if (writeHeader)147 {148 foreach (DataColumn column in dt.Columns)149 {150 worksheet.Cells[startX, startY + j] = column.Caption;151 ((Excel.Range)worksheet.Cells[startX, startY + j]).EntireColumn.AutoFit();152 j++;153 }154 i++;155 156 }157 foreach (DataRow row in dt.Rows)158 {159 for (j = 0; j < dt.Columns.Count; j++)160 worksheet.Cells[startX + i, startY + j] = row[j];161 i++;162 }163 string c1 = ConvertIndexToCellx(startX, startY);164 string c2 = ConvertIndexToCellx(startX + dt.Columns.Count, startY + dt.Rows.Count);165 Excel.Range range = worksheet.get_Range(c1, c2);166 167 range.EntireColumn.AutoFit();168 }169 public void SetRangeFont(string startDataCell, string endDataCell, bool isBold, bool isItalic, bool isOutlineFont, bool isShadow, Color color, double size)170 {171 Excel.Range cellRange = worksheet.get_Range(startDataCell, endDataCell);172 cellRange.Font.Bold = isBold;173 cellRange.Font.Italic = isItalic;174 cellRange.Font.OutlineFont = isOutlineFont;175 cellRange.Font.Shadow = isShadow;176 cellRange.Font.Color = System.Drawing.ColorTranslator.ToOle(color);177 cellRange.Font.Size = size;178179180 }181 public void SetRangeAutoFormat(string startDataCell, string endDataCell, Excel.XlRangeAutoFormat format)182 {183 Excel.Range cellRange = worksheet.get_Range(startDataCell, endDataCell);184 cellRange.AutoFormat(format, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);185 }186 public bool CreateChart(string startDataCell, string endDataCell, Excel.XlChartType chartType, string chartName, string Title, string categoryTitle, string valueTile)187 {188 try189 {190 Excel.Sheets charts = workBook.Charts;191 Excel.Chart xlChart = (Excel.Chart)charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);192193 dcoms.Add(charts);194 dcoms.Add(xlChart);195196 Excel.Range cellRange = worksheet.get_Range(startDataCell, endDataCell);197198 xlChart.ChartWizard(cellRange, chartType, Type.Missing, Excel.XlRowCol.xlColumns, 1, 0, true, Title, categoryTitle, valueTile, "aaa");199200 xlChart.Name = chartName;201202 Excel.ChartGroup grp = (Excel.ChartGroup)xlChart.ChartGroups(1);203 grp.GapWidth = 20;204 grp.VaryByCategories = true;205206207 Excel.Series s = (Excel.Series)grp.SeriesCollection(1);208 s.BarShape = Excel.XlBarShape.xlCylinder;209 s.HasDataLabels = true;210211 xlChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;212 xlChart.ChartTitle.Font.Size = 24;213 xlChart.ChartTitle.Shadow = true;214 xlChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlContinuous;215216 Excel.Axis valueAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);217 valueAxis.AxisTitle.Orientation = -90;218 Excel.Axis categoryAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);219 //categoryAxis.AxisTitle.Font.Name = "MS UI Gothic";220 xlChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAutomatic, worksheet.Name);221 return true;222 }223 catch (Exception ex)224 {225 throw new Exception(ex.Message);226227 }228229 return false;230 }231232 }233}234
转载于:https://www.cnblogs.com/skyfei/archive/2008/10/22/1316560.html
