服务器不安装Excel，实现导出Excel功能

呱少博客 2024-06-05 1 0

///

/// 导出为Excel ///

/// /// protected void btnExport_Click(object sender, EventArgs e) { ExportTest(); }

///

/// 导出测试 ///

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"); }

===========================================================================================================

using System;using System.Web;using System.Collections;using System.Collections.Generic;using System.Text.RegularExpressions;

namespace ExportExcel{ ///

/// 单元格值类型（日期作为文本处理） ///

public enum ValueType { String = 0, Number = 1 }

///

/// 行 ///

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; } }

///

/// 构造函数，生成一行 ///

/// 各单元格值 /// 是否自动设置值类型 public Row(string[] values, bool isAutoType) { if (values.Length > 256) { throw new Exception("Excel中不能超过256列！"); }

_cellsValue = values;

_rowText = "\n"; foreach (string cell in values) { ValueType vType = ValueType.String; if (isAutoType) { if (_regex.Match(cell).Success) { vType = ValueType.Number; } } _rowText += "" + cell + "\n"; } _rowText += "\n"; }

///

/// 构造函数，生成一行 ///

/// 各单元格值 /// 各单元格值类型 public Row(string[] values, ValueType[] valueTypes) { if (values.Length > 256 || valueTypes.Length > 256) { throw new Exception("Excel中不能超过256列！"); }

_cellsValue = values;

int i = 0; _rowText = "\n"; foreach (string cell in values) { ValueType vType = ValueType.String; if (i" + cell + "\n";

i++; } _rowText += "\n"; } }

///

/// 行集合 ///

public class _rows : IEnumerable { private List _rowList = new List(); 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; } }

///

/// 已使用行数 ///

public int Count { get { return _rowList.Count; } }

///

/// 添加标题行 ///

/// /// /// public Row AddTitle(string[] cells) { Row row = new Row(cells, false); _rowList.Add(row); return row; }

///

/// 添加标题行并设置列格式 ///

/// /// /// public Row AddTitle(string[] cells, ValueType[] valueTypes) { this._valueTypes = valueTypes; Row row = new Row(cells, false); _rowList.Add(row); return row; }

///

/// 添加行 ///

/// 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; } }

///

/// 删除行 ///

/// 行号 public void Delete(int index) { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } _rowList.RemoveAt(index); }

///

/// 获取行 ///

/// 行号 /// public Row this[int index] { get { if (index<0 || index >= this.Count) { throw new Exception("下标超出范围！"); } return _rowList[index]; } }

///

/// 遍历行 ///

/// public IEnumerator GetEnumerator() { return _rowList.GetEnumerator(); } }

///

/// 工作表类 ///

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; } }

///

/// 构造工作表 ///

/// 工作表名 public Sheet(string sheetName) { this._sheetName = sheetName; }

///

/// 冻结窗格 ///

/// 冻结线上方行数 /// 冻结线左边行数 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(); }

///

/// 工作表集合 ///

public class _sheets: IEnumerable { private List _sheetList = new List();

///

/// 工作表数量 ///

public int Count { get { return _sheetList.Count; } }

///

/// 添加工作表 ///

/// 工作表名 /// 工作表对象 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; }

///

/// 添加工作表 ///

/// 工作表对象 /// 工作表对象 public Sheet Add(Sheet sheet) { foreach (Sheet sht in _sheetList) { if (sht.SheetName == sheet.SheetName) { throw new Exception("同一工作簿中工作表名不能相同！"); } }

_sheetList.Add(sheet); return sheet; }

///

/// 删除工作表 ///

/// 工作表索引 public void Delete(int index) { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } _sheetList.RemoveAt(index); }

///

/// 获取工作表 ///

/// 工作表索引 /// public Sheet this[int index] { get { if (index < 0 || index >= this.Count) { throw new Exception("下标超出范围！"); } return _sheetList[index]; } }

///

/// 遍历工作表 ///

/// public IEnumerator GetEnumerator() { return _sheetList.GetEnumerator(); } }

///

/// Excel XML工作簿类 ///

public class ExcelXML { public _sheets Sheets = new _sheets();

private static string _appHead = " \n\n"; private static string _workBookHead = " \n\n";

#region 冻结窗格

///

/// 设置工作表选项 ///

/// /// /// private string GetWorksheetOptions(int topRowBottomPane, int leftColumnRightPane) { string s = "";

if (topRowBottomPane + leftColumnRightPane <= 0) { return s; }

s += "\n"; s += ""; s += "\n"; s += "\n";

//冻结行 if (topRowBottomPane > 0 && leftColumnRightPane <= 0) { s += "" + topRowBottomPane + "\n"; s += "" + topRowBottomPane + "\n"; //s += "2\n"; //s += "\n\n3\n\n\n2\n\n\n";

} //冻结列 else if (leftColumnRightPane > 0 && topRowBottomPane <= 0) { s += "" + leftColumnRightPane + "\n"; s += "" + leftColumnRightPane + "\n"; //s += "2\n"; //s += "\n\n5\n\n\n2\n\n\n"; } //冻结行、列 else { s += "" + topRowBottomPane + "\n"; s += "" + topRowBottomPane + "\n"; s += "" + leftColumnRightPane + "\n"; s += "" + leftColumnRightPane + "\n"; s += "0\n"; s += "\n\n3\n\n\n1\n\n\n2\n\n\n0\n\n\n"; }

s += "False\n"; s += "False\n"; s += "\n";

return s; }

#endregion

///

/// 导出到文件 ///

/// 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("\n"); HttpContext.Current.Response.Write("

\n");

//遍历行 foreach (Row row in sht.Rows) { HttpContext.Current.Response.Write("\n" + row.RowText); }

HttpContext.Current.Response.Write("\n

\n");

//冻结窗格选项 string sheetOptions = GetWorksheetOptions(sht.TopRowBottomPane, sht.LeftColumnRightPane); HttpContext.Current.Response.Write(sheetOptions);

HttpContext.Current.Response.Write("\n"); }

HttpContext.Current.Response.Write("\n"); HttpContext.Current.Response.End(); } }}

精彩文章

评论可见，请评论后查看内容，谢谢！！！评论后请刷新页面。

本文由用户于 2024-06-05 发布在夸智网，如有疑问，请联系我们。
本文链接：https://www.kuazhi.com/post/714076553.html

夸智网

服务器不安装Excel，实现导出Excel功能

柚子快报邀请码778899分享：Windows API

内存或磁盘空间不足，Microsoft Office Excel 无法再次打开或保存任何文档。 [问题点数：20分，结帖人wenyang2004]

发表评论取消回复

夸智网

服务器不安装Excel，实现导出Excel功能

柚子快报邀请码778899分享：Windows API

内存或磁盘空间不足，Microsoft Office Excel 无法再次打开或保存任何文档。 [问题点数：20分，结帖人wenyang2004]

相关文章

发表评论取消回复