答：
方法一、对于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, string strdate, 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:ID=\"Default\" 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 class ExcelWriter { private Stream stream; private BinaryWriter 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> public ExcelWriter(Stream stream) { this.stream = stream; writer = new BinaryWriter(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, string value) { 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, int value) { 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, double value) { 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, int col) { 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 void BeginWrite() { WriteUshortArray(clBegin); } /// <summary> /// Ends the writing operation, but do not close the stream /// </summary> public void EndWrite() { WriteUshortArray(clEnd); writer.Flush(); } }
由上面可以知道读取excel同理，去掉开头和结尾
方法四、
#region DateGridView导出到csv格式的Excel /// <summary> /// 常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。 /// </summary> /// <remarks> /// using System.IO; /// </remarks> /// <param name="dgv"></param> private void DataGridViewToExcel(DataGridView dgv) { SaveFileDialog dlg = new SaveFileDialog(); 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 += "\t"; } 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 += "\t"; } if (dgv.Rows[j].Cells[k].Value == null) columnValue += ""; else columnValue += 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 void DataGridViewToExcel2(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()); else Console.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 = new SaveFileDialog(); 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 = new FileInfo(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 = new Microsoft.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 + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); } #endregion
方法七、ASP.NET的话还可以用下面的代码：
/*==============下载自笔锋侠========================================== 文件名：ExcelXML.cs 功能说明：按照Excel体系结构封装的基础类，包含工作簿类、工作表集合、工作表、行集合、行 属性：普通类 其他： 创建者标识：笔锋侠 2010年02月03日 QQ:86994549 =====================================================================*/ using System; using System.Web; using System.Collections; using System.Collections.Generic; using System.Text.RegularExpressions; namespace ExportExcel { /// <summary> /// 单元格值类型（日期作为文本处理） /// </summary> public enum ValueType { String = 0, Number = 1 } /// <summary> /// 行 /// </summary> public class Row { private static string _patten = @"^\d{1,15}$"; private static Regex _regex = new Regex(_patten); private string[] _cellsValue; public string[] CellsValue { get { return _cellsValue; } set { _cellsValue = value; } } private string _rowText; public string RowText { get { return _rowText; } } /// <summary> /// 构造函数，生成一行 /// </summary> /// <param name="values">各单元格值</param> /// <param name="isAutoType">是否自动设置值类型</param> public Row(string[] values, bool isAutoType) { if (values.Length > 256) { throw new Exception("Excel中不能超过256列！"); } _cellsValue = values; _rowText = "<Row>\n"; foreach (string cell in values) { ValueType vType = ValueType.String; if (isAutoType) { if (_regex.Match(cell).Success) { vType = ValueType.Number; } } _rowText += "<Cell><Data ss:Type=\"" + vType.ToString() + "\">" + cell + "</Data></Cell>\n"; } _rowText += "</Row>\n"; } /// <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>\n"; foreach (string cell in values) { 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>\n"; i++; } _rowText += "</Row>\n"; } } /// <summary> /// 行集合 /// </summary> public class _rows : IEnumerable { private List<Row> _rowList = new List<Row>(); private bool _isAutoType = true; public bool IsAutoType { get { return _isAutoType; } set { _isAutoType = value; } } private ValueType[] _valueTypes; public ValueType[] ValueTypes { get { return _valueTypes; } set { _valueTypes = value; } } /// <summary> /// 已使用行数 /// </summary> public int Count { 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); return row; } /// <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); return row; } /// <summary> /// 添加行 /// </summary> /// <param name="cells"></param> public Row Add(string[] cells) { if (this.Count >= 65536) { throw new Exception("已经达到了Excel允许的最大行！"); } if (_valueTypes == null) { Row row = new Row(cells, _isAutoType); _rowList.Add(row); return row; } else { Row row = new Row(cells, _valueTypes); _rowList.Add(row); return row; } } /// <summary> /// 删除行 /// </summary> /// <param name="index">行号</param> public void Delete(int index) { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } _rowList.RemoveAt(index); } /// <summary> /// 获取行 /// </summary> /// <param name="index">行号</param> /// <returns></returns> public Row this[int index] { get { if (index<0 || index >= this.Count) { throw new Exception("下标超出范围！"); } return _rowList[index]; } } /// <summary> /// 遍历行 /// </summary> /// <returns></returns> public IEnumerator GetEnumerator() { return _rowList.GetEnumerator(); } } /// <summary> /// 工作表类 /// </summary> public class Sheet { private string _sheetName; public string SheetName { get { return _sheetName; } set { _sheetName = value; } } private int _topRowBottomPane = 0; public int TopRowBottomPane { get { return _topRowBottomPane; } } private int _leftColumnRightPane = 0; public int LeftColumnRightPane { get { return _leftColumnRightPane; } } /// <summary> /// 构造工作表 /// </summary> /// <param name="sheetName">工作表名</param> public Sheet(string sheetName) { this._sheetName = sheetName; } /// <summary> /// 冻结窗格 /// </summary> /// <param name="topRowBottomPane">冻结线上方行数</param> /// <param name="leftColumnRightPane">冻结线左边行数</param> public void Frozen(int topRowBottomPane, int leftColumnRightPane) { 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 int Count { get { return _sheetList.Count; } } /// <summary> /// 添加工作表 /// </summary> /// <param name="sheetName">工作表名</param> /// <returns>工作表对象</returns> public Sheet Add(string sheetName) { foreach (Sheet sht in _sheetList) { if (sht.SheetName == sheetName) { throw new Exception("同一工作簿中工作表名不能相同！"); } } Sheet sheet = new Sheet(sheetName); _sheetList.Add(sheet); return sheet; } /// <summary> /// 添加工作表 /// </summary> /// <param name="sheet">工作表对象</param> /// <returns>工作表对象</returns> public Sheet Add(Sheet sheet) { foreach (Sheet sht in _sheetList) { if (sht.SheetName == sheet.SheetName) { throw new Exception("同一工作簿中工作表名不能相同！"); } } _sheetList.Add(sheet); return sheet; } /// <summary> /// 删除工作表 /// </summary> /// <param name="index">工作表索引</param> public void Delete(int index) { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } _sheetList.RemoveAt(index); } /// <summary> /// 获取工作表 /// </summary> /// <param name="index">工作表索引</param> /// <returns></returns> public Sheet this[int index] { get { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } return _sheetList[index]; } } /// <summary> /// 遍历工作表 /// </summary> /// <returns></returns> public IEnumerator GetEnumerator() { return _sheetList.GetEnumerator(); } } /// <summary> /// Excel XML工作簿类 /// </summary> public class ExcelXML { public _sheets Sheets = new _sheets(); private static string _appHead = "<?xml version=\"1.0\"?> \n<?mso-application progid=\"Excel.Sheet\"?>\n"; private static string _workBookHead = "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" \n xmlns:o=\"urn:schemas-microsoft-com:office:office\" \n xmlns:x=\"urn:schemas-microsoft-com:office:excel\" \n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" \n xmlns:html=\"http://www.w3.org/TR/REC-html40\"> \n\n"; #region 冻结窗格 /// <summary> /// 设置工作表选项 /// </summary> /// <param name="topRowBottomPane"></param> /// <param name="leftColumnRightPane"></param> /// <returns></returns> private string GetWorksheetOptions(int topRowBottomPane, int leftColumnRightPane) { string s = ""; if (topRowBottomPane + leftColumnRightPane <= 0) { return s; } s += "<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n"; s += "<Selected/>"; s += "<FreezePanes/>\n"; s += "<FrozenNoSplit/>\n"; //冻结行 if (topRowBottomPane > 0 && leftColumnRightPane <= 0) { s += "<SplitHorizontal>" + topRowBottomPane + "</SplitHorizontal>\n"; s += "<TopRowBottomPane>" + topRowBottomPane + "</TopRowBottomPane>\n"; //s += "<ActivePane>2</ActivePane>\n"; //s += "<Panes>\n<Pane>\n<number>3</Number>\n</Pane>\n<Pane>\n<number>2</Number>\n</Pane>\n</Panes>\n"; } //冻结列 else if (leftColumnRightPane > 0 && topRowBottomPane <= 0) { s += "<SplitVertical>" + leftColumnRightPane + "</SplitVertical>\n"; s += "<LeftColumnRightPane>" + leftColumnRightPane + "</LeftColumnRightPane>\n"; //s += "<ActivePane>2</ActivePane>\n"; //s += "<Panes>\n<Pane>\n<number>5</Number>\n</Pane>\n<Pane>\n<number>2</Number>\n</Pane>\n</Panes>\n"; } //冻结行、列 else { s += "<SplitHorizontal>" + topRowBottomPane + "</SplitHorizontal>\n"; s += "<TopRowBottomPane>" + topRowBottomPane + "</TopRowBottomPane>\n"; s += "<SplitVertical>" + leftColumnRightPane + "</SplitVertical>\n"; s += "<LeftColumnRightPane>" + leftColumnRightPane + "</LeftColumnRightPane>\n"; s += "<ActivePane>0</ActivePane>\n"; s += "<Panes>\n<Pane>\n<Number>3</Number>\n</Pane>\n<Pane>\n<Number>1</Number>\n</Pane>\n<Pane>\n<Number>2</Number>\n</Pane>\n<Pane>\n<Number>0</Number> \n</Pane>\n</Panes>\n"; } s += "<ProtectObjects>False</ProtectObjects>\n"; s += "<ProtectScenarios>False</ProtectScenarios>\n"; s += "</WorksheetOptions>\n"; return s; } #endregion /// <summary> /// 导出到文件 /// </summary> /// <param name="fileName"></param> public void Export(string fileName) { 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 in Sheets) { HttpContext.Current.Response.Write("<Worksheet ss:Name=\"" + sht.SheetName + "\">\n"); HttpContext.Current.Response.Write("<Table>\n"); //遍历行 foreach (Row row in sht.Rows) { HttpContext.Current.Response.Write("\n" + row.RowText); } HttpContext.Current.Response.Write("\n</Table>\n"); //冻结窗格选项 string sheetOptions = GetWorksheetOptions(sht.TopRowBottomPane, sht.LeftColumnRightPane); HttpContext.Current.Response.Write(sheetOptions); HttpContext.Current.Response.Write("</Worksheet>\n"); } HttpContext.Current.Response.Write("</Workbook>\n"); HttpContext.Current.Response.End(); } } }
调用方式为：
/*================下载自笔锋侠======================================= 文件名：Default.aspx.cs 功能说明：通过多种方式向工作簿添加工作表及行（记录），并可以根据需要设置数值格式。 属性：功能测试代码 其他： 创建者标识：笔锋侠 2010年02月03日 QQ:86994549 =====================================================================*/ using System; namespace ExportExcel { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } /// <summary> /// 导出为Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExport_Click(object sender, EventArgs e) { ExportTest(); } /// <summary> /// 导出测试 /// </summary> protected void ExportTest() { ExcelXML excel = new ExcelXML(); excel.Sheets.Add("Sheet1"); excel.Sheets.Add("Sheet5"); excel.Sheets.Add("工作表1"); excel.Sheets[0].Rows.AddTitle(new string[] { "编号", "部门", "日期" }, new ValueType[] { 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"); } } }