摘要：
答：方法一、对于07/10及以后的版本，可以采用openxmlsdk，因为07/10都是openxml标准的，利用openxmlsdk可以不用安装office而对office文件进行操作。若03不安装office，可以使用NPOI。可以考虑将数据发送到服务端，从服务端生成excel文件，然后再传输会客户端。这样客户端就不需要安装任何office软件了，只要在服务端安装就可以了。这种格式的文件不论在程序中还是在Excel中都可以很好地支持。excel可以保存为或打开这种格式。
答：
方法一、对于07/10及以后的版本，可以采用openxml sdk，因为07/10都是open xml标准的，利用open xml sdk可以不用安装office而对office文件进行操作。而且速度快。
若03不安装office，可以使用NPOI 。
可以考虑将数据发送到服务端，从服务端生成excel文件，然后再传输会客户端。这样客户端就不需要安装任何office软件了，只要在服务端安装就可以了。
方法二、导出为文本格式，每条记录占一行，每列使用制表符分隔。这种格式的文件不论在程序中还是在Excel中都可以很好地支持。excel可以保存为或打开这种格式。
方法三、pengzhiq的专栏（http://blog.csdn.net/pengzhiq/article/details/6004365）说的两种方式，
第一种方式：写 xml 比较简单dataset.WriteXml(strXMLFileName, System.Data.XmlWriteMode.WriteSchema);
代码摘自心中有你（http://hi.baidu.com/sbiweeq/item/75b16e4fe85a270cc01613be）的《c# 导出Excel Xml格式 不用安装Excel也不用引入任何组件》，
public static void CreateExcel(string filepath, int roms, int troms, int zroms, stringstrdate, Model.Form8 form8) { if (filepath != "") { StreamWriter rw = new StreamWriter(filepath, false, Encoding.GetEncoding("UTF-8"), 10240); rw.WriteLine("<?xml version="1.0"?>"); rw.WriteLine("<?mso-application progid="Excel.Sheet"?>"); rw.WriteLine("<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">"); rw.WriteLine("<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">"); rw.WriteLine("<Created>1996-12-17T01:32:42Z</Created>"); rw.WriteLine("<LastSaved>2000-11-18T06:53:49Z</LastSaved>"); rw.WriteLine("<Version>11.8107</Version>"); rw.WriteLine("</DocumentProperties>"); rw.WriteLine("<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">"); rw.WriteLine("<RemovePersonalInformation/>"); rw.WriteLine("</OfficeDocumentSettings>"); rw.WriteLine("<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">"); rw.WriteLine("<WindowHeight>4530</WindowHeight>"); rw.WriteLine("<WindowWidth>8505</WindowWidth>"); rw.WriteLine("<WindowTopX>480</WindowTopX>"); rw.WriteLine("<WindowTopY>120</WindowTopY>"); rw.WriteLine("<AcceptLabelsInFormulas/>"); rw.WriteLine("<ProtectStructure>False</ProtectStructure>"); rw.WriteLine("<ProtectWindows>False</ProtectWindows>"); rw.WriteLine("</ExcelWorkbook>"); rw.WriteLine("<Styles>"); rw.WriteLine("<Style ss: ss:Name="Normal">"); rw.WriteLine("<Alignment ss:Vertical="Bottom"/>"); rw.WriteLine("<Borders/>"); rw.WriteLine("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>"); rw.WriteLine("<Interior/>"); rw.WriteLine("<NumberFormat/>"); rw.WriteLine("<Protection/>"); rw.WriteLine("</Style>"); rw.WriteLine("<Style ss:ID="s24">"); rw.WriteLine("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>"); rw.WriteLine("<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>"); rw.WriteLine("<Borders>"); rw.WriteLine("<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("</Borders>"); rw.WriteLine("<NumberFormat ss:Format="@"/>"); rw.WriteLine("</Style>"); rw.WriteLine("<Style ss:ID="s25">"); rw.WriteLine("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>"); rw.WriteLine("<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>"); rw.WriteLine("<Borders>"); rw.WriteLine("<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>"); rw.WriteLine("</Borders>"); rw.WriteLine("</Style>"); rw.WriteLine("<Style ss:ID="s26">"); rw.WriteLine("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>"); rw.WriteLine("<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>"); rw.WriteLine("</Style>"); rw.WriteLine("<Style ss:ID="s27">"); rw.WriteLine("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>"); rw.WriteLine("<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>"); rw.WriteLine("</Style>"); rw.WriteLine("<Style ss:ID="m24861836">"); rw.WriteLine("<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>"); rw.WriteLine("<Borders/>"); rw.WriteLine("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="20" ss:Bold="1"/>"); rw.WriteLine("<NumberFormat/>"); rw.WriteLine("<Protection ss:Protected="0"/>"); rw.WriteLine("</Style>"); rw.WriteLine("<Style ss:ID="s28">"); rw.WriteLine("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>"); rw.WriteLine("<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>"); rw.WriteLine("<NumberFormat ss:Format="@"/>"); rw.WriteLine("</Style>"); rw.WriteLine("</Styles>"); //sheet rw.WriteLine("<Worksheet ss:Name="Sheet1">"); rw.WriteLine("<Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="75" ss:DefaultRowHeight="14.25">"); if (strdate != null) { string[] str = strdate.Split('￣'); int cou = 1; //设置表头 rw.WriteLine("<Row ss:Height="31.5">"); rw.WriteLine("<Cell ss:MergeAcross="7" ss:StyleID="m24861836"><Data ss:Type="String">123123</Data></Cell>"); rw.WriteLine("</Row>"); rw.WriteLine("<Row>"); rw.WriteLine("<Cell ss:StyleID="s26"><Data ss:Type="String">123123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s27"/>"); rw.WriteLine("<Cell ss:StyleID="s26"><Data ss:Type="String">123</Data></Cell>"); rw.WriteLine("<Cell ss:MergeAcross="2" ss:StyleID="s27"><Data ss:Type="String">asdasd</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s26"><Data ss:Type="String">123123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s28"><Data ss:Type="Number">123</Data></Cell>"); rw.WriteLine("</Row>"); rw.WriteLine("<Row>"); rw.WriteLine("<Cell ss:MergeAcross="1" ss:StyleID="s25"><Data ss:Type="String">q123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); rw.WriteLine("</Row>"); for (int i = 0; i < roms; i++) { rw.WriteLine("<Row>"); for (int j = 0; j < 8; j++) { if (j == 0) { if (i <troms) { rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); } if (i >= troms && i < (troms +zroms)) { rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">123</Data></Cell>"); } } else{ if (i < (troms +zroms)) { try{ if(isNum(str[cou])) { rw.WriteLine("<Cell ss:StyleID="s24"><Data ss:Type="Number">" + str[cou] + "</Data></Cell>"); } else{ rw.WriteLine("<Cell ss:StyleID="s25"><Data ss:Type="String">" + str[cou] + "</Data></Cell>"); } cou++; } catch(Exception ex) { } } } } rw.WriteLine("</Row>"); } } //设置表尾 rw.WriteLine("<Row>"); rw.WriteLine("<Cell/>"); rw.WriteLine("<Cell ss:StyleID="s26"><Data ss:Type="String">123：</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s27"><Data ss:Type="String">" + form8.P302 + "</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s26"><Data ss:Type="String">123：</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s27"><Data ss:Type="String">" + form8.P303 + "</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s26"><Data ss:Type="String">123：</Data></Cell>"); rw.WriteLine("<Cell ss:StyleID="s27"><Data ss:Type="String">" + form8.P304 + "</Data></Cell>"); rw.WriteLine("</Row>"); rw.WriteLine("</Table>"); rw.WriteLine("<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">"); rw.WriteLine("<Selected/>"); rw.WriteLine("<ProtectObjects>False</ProtectObjects>"); rw.WriteLine("<ProtectScenarios>False</ProtectScenarios>"); rw.WriteLine("</WorksheetOptions>"); rw.WriteLine("</Worksheet>"); rw.WriteLine("<Worksheet ss:Name="Sheet2">"); rw.WriteLine("<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>"); rw.WriteLine("<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">"); rw.WriteLine("<Selected/>"); rw.WriteLine("<ProtectObjects>False</ProtectObjects>"); rw.WriteLine("<ProtectScenarios>False</ProtectScenarios>"); rw.WriteLine("</WorksheetOptions>"); rw.WriteLine("</Worksheet>"); rw.WriteLine("<Worksheet ss:Name="Sheet3">"); rw.WriteLine("<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>"); rw.WriteLine("<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">"); rw.WriteLine("<Selected/>"); rw.WriteLine("<ProtectObjects>False</ProtectObjects>"); rw.WriteLine("<ProtectScenarios>False</ProtectScenarios>"); rw.WriteLine("</WorksheetOptions>"); rw.WriteLine("</Worksheet>"); //sheet rw.WriteLine("</Workbook>"); rw.Flush(); rw.Close(); } }
第二种方式：先得了解excel文件的文件流格式。
public classExcelWriter { privateStream stream; privateBinaryWriter writer; private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0}; private ushort[] clEnd = { 0x0A, 00}; private void WriteUshortArray(ushort[] value) { for (int i = 0; i < value.Length; i++) writer.Write(value[i]); } /// <summary> ///Initializes a new instance of the <see cref="ExcelWriter"/>class. /// </summary> /// <param name="stream">The stream.</param> publicExcelWriter(Stream stream) { this.stream =stream; writer = newBinaryWriter(stream); } /// <summary> ///Writes the text cell value. /// </summary> /// <param name="row">The row.</param> /// <param name="col">The col.</param> /// <param name="value">The string value.</param> public void WriteCell(int row, int col, stringvalue) { ushort[] clData = { 0x0204, 0, 0, 0, 0, 0}; int iLen =value.Length; byte[] plainText =Encoding.ASCII.GetBytes(value); clData[1] = (ushort)(8 +iLen); clData[2] = (ushort)row; clData[3] = (ushort)col; clData[5] = (ushort)iLen; WriteUshortArray(clData); writer.Write(plainText); } /// <summary> ///Writes the integer cell value. /// </summary> /// <param name="row">The row number.</param> /// <param name="col">The column number.</param> /// <param name="value">The value.</param> public void WriteCell(int row, int col, intvalue) { ushort[] clData = { 0x027E, 10, 0, 0, 0}; clData[2] = (ushort)row; clData[3] = (ushort)col; WriteUshortArray(clData); int iValue = (value << 2) | 2; writer.Write(iValue); } /// <summary> ///Writes the double cell value. /// </summary> /// <param name="row">The row number.</param> /// <param name="col">The column number.</param> /// <param name="value">The value.</param> public void WriteCell(int row, int col, doublevalue) { ushort[] clData = { 0x0203, 14, 0, 0, 0}; clData[2] = (ushort)row; clData[3] = (ushort)col; WriteUshortArray(clData); writer.Write(value); } /// <summary> ///Writes the empty cell. /// </summary> /// <param name="row">The row number.</param> /// <param name="col">The column number.</param> public void WriteCell(int row, intcol) { ushort[] clData = { 0x0201, 6, 0, 0, 0x17}; clData[2] = (ushort)row; clData[3] = (ushort)col; WriteUshortArray(clData); } /// <summary> ///Must be called once for creating XLS file header /// </summary> public voidBeginWrite() { WriteUshortArray(clBegin); } /// <summary> ///Ends the writing operation, but do not close the stream /// </summary> public voidEndWrite() { WriteUshortArray(clEnd); writer.Flush(); } }
由上面可以知道读取excel同理，去掉开头和结尾
方法四、
#region DateGridView导出到csv格式的Excel /// <summary> ///常用方法，列之间加 ，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。 /// </summary> /// <remarks> ///using System.IO; /// </remarks> /// <param name="dgv"></param> private voidDataGridViewToExcel(DataGridView dgv) { SaveFileDialog dlg = newSaveFileDialog(); dlg.Filter = "Execl files (*.xls)|*.xls"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.CreatePrompt = true; dlg.Title = "保存为Excel文件"; if (dlg.ShowDialog() ==DialogResult.OK) { Stream myStream; myStream =dlg.OpenFile(); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string columnTitle = ""; try{ //写入列标题 for (int i = 0; i < dgv.ColumnCount; i++) { if (i > 0) { columnTitle += ""; } columnTitle +=dgv.Columns[i].HeaderText; } sw.WriteLine(columnTitle); //写入列内容 for (int j = 0; j < dgv.Rows.Count; j++) { string columnValue = ""; for (int k = 0; k < dgv.Columns.Count; k++) { if (k > 0) { columnValue += ""; } if (dgv.Rows[j].Cells[k].Value == null) columnValue += ""; elsecolumnValue +=dgv.Rows[j].Cells[k].Value.ToString().Trim(); } sw.WriteLine(columnValue); } sw.Close(); myStream.Close(); } catch(Exception e) { MessageBox.Show(e.ToString()); } finally{ sw.Close(); myStream.Close(); } } } #endregion
方法五、只要有Word/Excel的相关Com的DLL,就可实现操作导入导出操作,但不能打开.
Excel: Excel.dll、 office.dll、 vbide.dll
word: word.dll、 office.dll、 vbide.dll
方法六、还是要安装Office了，但能检测是否安装了Office
#region DataGridView导出到Excel，有一定的判断性 /// <summary> ///方法，导出DataGridView中的数据到Excel文件 /// </summary> /// <remarks> ///add com "Microsoft Excel 11.0 Object Library" ///using Excel=Microsoft.Office.Interop.Excel; ///using System.Reflection; /// </remarks> /// <param name= "dgv">DataGridView </param> public static voidDataGridViewToExcel2(DataGridView dgv) { //用GetTypeFromProgID来获取是否安装了Office，比较可靠。 //通过 class id , prog id 来拿, 看结果测试是否安装才是王道 prog id 可信, clsid 不可信; //另外： //try //{ //OfficeExcel.Application oExcel = oExcel = new OfficeExcel.Application(); //} //catch //{ //MessageBox.Show("本机没安装 Excel"); //} //上面的验证方式也不好，就怕Excel.Application app=new Excel.ApplicationClass();这句会抛出异常. string projid1 = "Excel.Application"; string projid2 = "Excel.Application.15"; System.Type t1 =System.Type.GetTypeFromProgID(projid1); if (t1 != null) Console.WriteLine(t1.ToString()); elseConsole.WriteLine("cannot get {0}", projid1); System.Type t2 =System.Type.GetTypeFromProgID(projid2); if (t2 != null) { } else{ MessageBox.Show("The office have not installed!Please install office first!"); return; } #region 验证可操作性SaveFileDialog dlg = newSaveFileDialog(); dlg.DefaultExt = "xls "; dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; dlg.InitialDirectory =Directory.GetCurrentDirectory(); if (dlg.ShowDialog() ==DialogResult.Cancel) { return; } string fileNameString =dlg.FileName; if (fileNameString.Trim() == " ") { return; } //定义表格内数据的行数和列数 int rowscount =dgv.Rows.Count; int colscount =dgv.Columns.Count; //行数必须大于0 if (rowscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列数必须大于0 if (colscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //行数不可以大于65536 if (rowscount > 65536) { MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列数不可以大于255 if (colscount > 255) { MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //验证以fileNameString命名的文件是否存在，如果存在删除它 FileInfo file = newFileInfo(fileNameString); if(file.Exists) { try{ file.Delete(); } catch(Exception error) { MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } } #endregion Excel.Application objExcel = null; Excel.Workbook objWorkbook = null; Excel.Worksheet objsheet = null; try{ //申明对象 objExcel = newMicrosoft.Office.Interop.Excel.Application(); objWorkbook =objExcel.Workbooks.Add(Missing.Value); objsheet =(Excel.Worksheet)objWorkbook.ActiveSheet; //设置EXCEL不可见 objExcel.Visible = false; //向Excel中写入表格的表头 int displayColumnsCount = 1; for (int i = 0; i <= dgv.ColumnCount - 1; i++) { if (dgv.Columns[i].Visible == true) { objExcel.Cells[1, displayColumnsCount] =dgv.Columns[i].HeaderText.Trim(); displayColumnsCount++; } } //向Excel中逐行逐列写入表格中的数据 for (int row = 0; row <= dgv.RowCount - 1; row++) { displayColumnsCount = 1; for (int col = 0; col < colscount; col++) { if (dgv.Columns[col].Visible == true) { try{ objExcel.Cells[row + 2, displayColumnsCount] =dgv.Rows[row].Cells[col].Value.ToString().Trim(); displayColumnsCount++; } catch(Exception) { } } } } //保存文件 objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch(Exception error) { MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } finally{ //关闭Excel应用 if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value); if (objExcel.Workbooks != null) objExcel.Workbooks.Close(); if (objExcel != null) objExcel.Quit(); //objsheet = null; //objWorkbook = null; //objExcel = null; //释放资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(objsheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel); System.GC.Collect();//强制垃圾回收 } MessageBox.Show(fileNameString + " 导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); } #endregion
方法七、ASP.NET的话还可以用下面的代码：
/*==============下载自笔锋侠========================================== 文件名：ExcelXML.cs 功能说明：按照Excel体系结构封装的基础类，包含工作簿类、工作表集合、工作表、行集合、行 属性：普通类 其他： 创建者标识：笔锋侠 2010年02月03日 QQ:86994549 =====================================================================*/ usingSystem; usingSystem.Web; usingSystem.Collections; usingSystem.Collections.Generic; usingSystem.Text.RegularExpressions; namespaceExportExcel { /// <summary> ///单元格值类型（日期作为文本处理） /// </summary> public enumValueType { String = 0, Number = 1} /// <summary> ///行 /// </summary> public classRow { private static string _patten = @"^d{1,15}$"; private static Regex _regex = newRegex(_patten); private string[] _cellsValue; public string[] CellsValue { get { return_cellsValue; } set { _cellsValue =value; } } private string_rowText; public stringRowText { get { return_rowText; } } /// <summary> ///构造函数，生成一行 /// </summary> /// <param name="values">各单元格值</param> /// <param name="isAutoType">是否自动设置值类型</param> public Row(string[] values, boolisAutoType) { if (values.Length > 256) { throw new Exception("Excel中不能超过256列！"); } _cellsValue =values; _rowText = "<Row> "; foreach (string cell invalues) { ValueType vType =ValueType.String; if(isAutoType) { if(_regex.Match(cell).Success) { vType =ValueType.Number; } } _rowText += "<Cell><Data ss:Type="" + vType.ToString() + "">" + cell + "</Data></Cell> "; } _rowText += "</Row> "; } /// <summary> ///构造函数，生成一行 /// </summary> /// <param name="values">各单元格值</param> /// <param name="valueTypes">各单元格值类型</param> public Row(string[] values, ValueType[] valueTypes) { if (values.Length > 256 || valueTypes.Length > 256) { throw new Exception("Excel中不能超过256列！"); } _cellsValue =values; int i = 0; _rowText = "<Row> "; foreach (string cell invalues) { ValueType vType =ValueType.String; if (i<valueTypes.Length) { vType =valueTypes[i]; if (vType ==ValueType.Number) { if (!_regex.Match(cell).Success) { vType =ValueType.String; } } } _rowText += "<Cell><Data ss:Type="" + vType.ToString() + "">" + cell + "</Data></Cell> "; i++; } _rowText += "</Row> "; } } /// <summary> ///行集合 /// </summary> public class_rows : IEnumerable { private List<Row> _rowList = new List<Row>(); private bool _isAutoType = true; public boolIsAutoType { get { return_isAutoType; } set { _isAutoType =value; } } privateValueType[] _valueTypes; publicValueType[] ValueTypes { get { return_valueTypes; } set { _valueTypes =value; } } /// <summary> ///已使用行数 /// </summary> public intCount { get { return_rowList.Count; } } /// <summary> ///添加标题行 /// </summary> /// <param name="cells"></param> /// <param name="valueTypes"></param> /// <returns></returns> public Row AddTitle(string[] cells) { Row row = new Row(cells, false); _rowList.Add(row); returnrow; } /// <summary> ///添加标题行并设置列格式 /// </summary> /// <param name="cells"></param> /// <param name="valueTypes"></param> /// <returns></returns> public Row AddTitle(string[] cells, ValueType[] valueTypes) { this._valueTypes =valueTypes; Row row = new Row(cells, false); _rowList.Add(row); returnrow; } /// <summary> ///添加行 /// </summary> /// <param name="cells"></param> public Row Add(string[] cells) { if (this.Count >= 65536) { throw new Exception("已经达到了Excel允许的最大行！"); } if (_valueTypes == null) { Row row = newRow(cells, _isAutoType); _rowList.Add(row); returnrow; } else{ Row row = newRow(cells, _valueTypes); _rowList.Add(row); returnrow; } } /// <summary> ///删除行 /// </summary> /// <param name="index">行号</param> public void Delete(intindex) { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } _rowList.RemoveAt(index); } /// <summary> ///获取行 /// </summary> /// <param name="index">行号</param> /// <returns></returns> public Row this[intindex] { get{ if (index<0 || index >= this.Count) { throw new Exception("下标超出范围！"); } return_rowList[index]; } } /// <summary> ///遍历行 /// </summary> /// <returns></returns> publicIEnumerator GetEnumerator() { return_rowList.GetEnumerator(); } } /// <summary> ///工作表类 /// </summary> public classSheet { private string_sheetName; public stringSheetName { get { return_sheetName; } set { _sheetName =value; } } private int _topRowBottomPane = 0; public intTopRowBottomPane { get { return_topRowBottomPane; } } private int _leftColumnRightPane = 0; public intLeftColumnRightPane { get { return_leftColumnRightPane; } } /// <summary> ///构造工作表 /// </summary> /// <param name="sheetName">工作表名</param> public Sheet(stringsheetName) { this._sheetName =sheetName; } /// <summary> ///冻结窗格 /// </summary> /// <param name="topRowBottomPane">冻结线上方行数</param> /// <param name="leftColumnRightPane">冻结线左边行数</param> public void Frozen(int topRowBottomPane, intleftColumnRightPane) { if (topRowBottomPane < 0 || topRowBottomPane >= 65536) { throw new Exception("索引超出范围！"); } if (leftColumnRightPane < 0 || leftColumnRightPane >= 256) { throw new Exception("索引超出范围！"); } this._topRowBottomPane =topRowBottomPane; this._leftColumnRightPane =leftColumnRightPane; } public _rows Rows = new_rows(); } /// <summary> ///工作表集合 /// </summary> public class_sheets: IEnumerable { private List<Sheet> _sheetList = new List<Sheet>(); /// <summary> ///工作表数量 /// </summary> public intCount { get { return_sheetList.Count; } } /// <summary> ///添加工作表 /// </summary> /// <param name="sheetName">工作表名</param> /// <returns>工作表对象</returns> public Sheet Add(stringsheetName) { foreach (Sheet sht in_sheetList) { if (sht.SheetName ==sheetName) { throw new Exception("同一工作簿中工作表名不能相同！"); } } Sheet sheet = newSheet(sheetName); _sheetList.Add(sheet); returnsheet; } /// <summary> ///添加工作表 /// </summary> /// <param name="sheet">工作表对象</param> /// <returns>工作表对象</returns> publicSheet Add(Sheet sheet) { foreach (Sheet sht in_sheetList) { if (sht.SheetName ==sheet.SheetName) { throw new Exception("同一工作簿中工作表名不能相同！"); } } _sheetList.Add(sheet); returnsheet; } /// <summary> ///删除工作表 /// </summary> /// <param name="index">工作表索引</param> public void Delete(intindex) { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } _sheetList.RemoveAt(index); } /// <summary> ///获取工作表 /// </summary> /// <param name="index">工作表索引</param> /// <returns></returns> public Sheet this[intindex] { get{ if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } return_sheetList[index]; } } /// <summary> ///遍历工作表 /// </summary> /// <returns></returns> publicIEnumerator GetEnumerator() { return_sheetList.GetEnumerator(); } } /// <summary> ///Excel XML工作簿类 /// </summary> public classExcelXML { public _sheets Sheets = new_sheets(); private static string _appHead = "<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> "; private static string _workBookHead = "<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> "; #region 冻结窗格 /// <summary> ///设置工作表选项 /// </summary> /// <param name="topRowBottomPane"></param> /// <param name="leftColumnRightPane"></param> /// <returns></returns> private string GetWorksheetOptions(int topRowBottomPane, intleftColumnRightPane) { string s = ""; if (topRowBottomPane + leftColumnRightPane <= 0) { returns; } s += "<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> "; s += "<Selected/>"; s += "<FreezePanes/> "; s += "<FrozenNoSplit/> "; //冻结行 if (topRowBottomPane > 0 && leftColumnRightPane <= 0) { s += "<SplitHorizontal>" + topRowBottomPane + "</SplitHorizontal> "; s += "<TopRowBottomPane>" + topRowBottomPane + "</TopRowBottomPane> "; //s += "<ActivePane>2</ActivePane> "; //s += "<Panes> <Pane> <number>3</Number> </Pane> <Pane> <number>2</Number> </Pane> </Panes> "; } //冻结列 else if (leftColumnRightPane > 0 && topRowBottomPane <= 0) { s += "<SplitVertical>" + leftColumnRightPane + "</SplitVertical> "; s += "<LeftColumnRightPane>" + leftColumnRightPane + "</LeftColumnRightPane> "; //s += "<ActivePane>2</ActivePane> "; //s += "<Panes> <Pane> <number>5</Number> </Pane> <Pane> <number>2</Number> </Pane> </Panes> "; } //冻结行、列 else{ s += "<SplitHorizontal>" + topRowBottomPane + "</SplitHorizontal> "; s += "<TopRowBottomPane>" + topRowBottomPane + "</TopRowBottomPane> "; s += "<SplitVertical>" + leftColumnRightPane + "</SplitVertical> "; s += "<LeftColumnRightPane>" + leftColumnRightPane + "</LeftColumnRightPane> "; s += "<ActivePane>0</ActivePane> "; s += "<Panes> <Pane> <Number>3</Number> </Pane> <Pane> <Number>1</Number> </Pane> <Pane> <Number>2</Number> </Pane> <Pane> <Number>0</Number> </Pane> </Panes> "; } s += "<ProtectObjects>False</ProtectObjects> "; s += "<ProtectScenarios>False</ProtectScenarios> "; s += "</WorksheetOptions> "; returns; } #endregion /// <summary> ///导出到文件 /// </summary> /// <param name="fileName"></param> public void Export(stringfileName) { if (this.Sheets.Count < 1) { throw new Exception("没有工作表！"); } string fName =System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(fileName)); HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fName + ".xls"); HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.UTF8; HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword HttpContext.Current.Response.Write(_appHead); HttpContext.Current.Response.Write(_workBookHead); //遍历工作表 foreach (Sheet sht inSheets) { HttpContext.Current.Response.Write("<Worksheet ss:Name="" + sht.SheetName + ""> "); HttpContext.Current.Response.Write("<Table> "); //遍历行 foreach (Row row insht.Rows) { HttpContext.Current.Response.Write("" +row.RowText); } HttpContext.Current.Response.Write("</Table> "); //冻结窗格选项 string sheetOptions =GetWorksheetOptions(sht.TopRowBottomPane, sht.LeftColumnRightPane); HttpContext.Current.Response.Write(sheetOptions); HttpContext.Current.Response.Write("</Worksheet> "); } HttpContext.Current.Response.Write("</Workbook> "); HttpContext.Current.Response.End(); } } }
调用方式为：
/*================下载自笔锋侠======================================= 文件名：Default.aspx.cs 功能说明：通过多种方式向工作簿添加工作表及行（记录），并可以根据需要设置数值格式。 属性：功能测试代码 其他： 创建者标识：笔锋侠 2010年02月03日 QQ:86994549 =====================================================================*/ usingSystem; namespaceExportExcel { public partial class_Default : System.Web.UI.Page { protected void Page_Load(objectsender, EventArgs e) { } /// <summary> ///导出为Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExport_Click(objectsender, EventArgs e) { ExportTest(); } /// <summary> ///导出测试 /// </summary> protected voidExportTest() { ExcelXML excel = newExcelXML(); excel.Sheets.Add("Sheet1"); excel.Sheets.Add("Sheet5"); excel.Sheets.Add("工作表1"); excel.Sheets[0].Rows.AddTitle(new string[] { "编号", "部门", "日期" }, newValueType[] { ValueType.Number, ValueType.String, ValueType.String }); excel.Sheets[0].Frozen(0, 1); excel.Sheets[0].Rows.Add(new string[] { "1a", "财务部", "2009-1-5"}); excel.Sheets[0].Rows.Add(new string[] { "02", "市场部", "2010-01-20 15:35"}); excel.Sheets[0].Rows.Add(new string[] { "3", "销售部", "15:20:37"}); excel.Sheets[0].Rows.Add(new string[] { "", "销售部", "15:20:37"}); excel.Sheets[0].Rows.Add(new string[] { "0", "销售部", "15:20:37"}); excel.Sheets[0].Rows.Add(new string[] { "1234567890", "销售部", "15:20:37"}); excel.Sheets[0].Rows.Add(new string[] { "12345678901", "销售部", "15:20:37"}); excel.Sheets[0].Rows.Add(new string[] { "123456789012", "销售部", "15:20:37"}); excel.Sheets[1].Rows.Add(new string[] { "1", "2", "3"}); excel.Sheets[1].Rows.Add(new string[] { "1", "测字测试", "3"}); excel.Sheets[1].Frozen(1, 0); excel.Sheets[2].Rows.Add(new string[] { "1", "2", "3"}); excel.Sheets[2].Rows.Add(new string[] { "1", "测字测试", "3"}); Sheet sheet = new Sheet("测试"); sheet.Rows.AddTitle(new string[] { "编号", "部门", "日期", "值"}); for (int i = 1; i < 100; i++) { sheet.Rows.Add(new string[] { i.ToString(), "部门_" + i % 3, DateTime.Today.AddDays(i % 5).ToString(), (i * 100).ToString() }); } sheet.Frozen(2, 1); excel.Sheets.Add(sheet); excel.Export(DateTime.Now.ToString("yyyyMMdd-HHmmss_") + "Export"); } } }