以下是我简单封装的一个ExcelHelper
using System
using System.Data
using System.Configuration
using System.Web
using System.Web.Security
using System.Web.UI
using System.Web.UI.HtmlControls
using System.Web.UI.WebControls
using System.Web.UI.WebControls.WebParts
using System.Collections.Generic
using org.in2bits.MyXls
using org.in2bits.MyXls.ByteUtil
/// <summary>
///ExcelHelper 的摘要说明
/// </summary>
public class ExcelHelper
{
protected ExcelHelper()
{
}
public class TableStruct
{
private string _TableName
private string _TableCaption
private List<ColumnStruct>_ColumnInfoAry
public string TableName
{
get
{
if (string.IsNullOrEmpty(_TableName))
{
return string.Empty
}
return _TableName
}
set
{
_TableName = value
}
}
public string TableCaption
{
get
{
if (string.IsNullOrEmpty(_TableCaption))
{
return TableName
}
return _TableCaption
}
set
{
_TableCaption = value
}
}
public List<ColumnStruct>ColumnInfoAry
{
get
{
if (_ColumnInfoAry == null)
{
_ColumnInfoAry = new List<ColumnStruct>()
}
return _ColumnInfoAry
}
}
public void AddColumnInfo(ColumnStruct ColumnInfo)
{
if (ColumnInfo == null)
{
return
}
if (_ColumnInfoAry == null)
{
_ColumnInfoAry = new List<ColumnStruct>()
}
foreach (ColumnStruct col in _ColumnInfoAry)
{
if (col.ColumnName.Equals(ColumnInfo.ColumnName, StringComparison.OrdinalIgnoreCase))
{
return
}
}
_ColumnInfoAry.Add(ColumnInfo)
}
public ColumnStruct GetColumnInfo(string ColumnName)
{
if (string.IsNullOrEmpty(ColumnName) )
{
return null
}
if (_ColumnInfoAry == null)
{
return null
}
ColumnStruct ColumnInfo = null
foreach (ColumnStruct col in _ColumnInfoAry)
{
if (col.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase))
{
ColumnInfo = col
}
}
return ColumnInfo
}
}
public class ColumnStruct
{
private string _ColumnName
private string _ColumnCaption
private string _ColumnTextFormat
public string ColumnName
{
get
{
if (string.IsNullOrEmpty(_ColumnName))
{
return string.Empty
}
return _ColumnName
}
set
{
_ColumnName = value
}
}
public string ColumnCaption
{
get
{
if (string.IsNullOrEmpty(_ColumnCaption))
{
return ColumnName
}
return _ColumnCaption
}
set
{
_ColumnCaption = value
}
}
public string ColumnTextFormat
{
get
{
if (string.IsNullOrEmpty(_ColumnTextFormat))
{
return string.Empty
}
return _ColumnTextFormat
}
set
{
_ColumnTextFormat = value
}
}
}
public static void ExportToExcel(DataSet ds,List<TableStruct>TableInfoAry)
{
if (ds == null)
{
ds = new DataSet()
}
if ( TableInfoAry == null )
{
TableInfoAry = new List<TableStruct>()
}
XlsDocument xls = new XlsDocument()
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmssffff",System.Globalization.DateTimeFormatInfo.InvariantInfo)
xls.SummaryInformation.Author = "wangmh"//填加xls文件作者信息
xls.SummaryInformation.NameOfCreatingApplication = "Microsoft Excel"//填加xls文件创建程序信息
xls.SummaryInformation.LastSavedBy = "wangmh"//填加xls文件最后保存者信息
xls.SummaryInformation.Comments = "Gwm"//填加xls文件作者信息
xls.SummaryInformation.Title = "Gwm"//填加xls文件标题信息
xls.SummaryInformation.Subject = "Gwm"//填加文件主题信息
xls.DocumentSummaryInformation.Company = "Gwm"//填加文件公司信息
foreach (TableStruct TableInfo in TableInfoAry)
{
DataTable dt = ds.Tables[TableInfo.TableName]
if (dt == null)
{
continue
}
Worksheet sheet = xls.Workbook.Worksheets.Add(TableInfo.TableCaption)
//设置标头栏
ushort ColumnIndex = 1
foreach (ColumnStruct ColStruct in TableInfo.ColumnInfoAry)
{
ushort RowIndex = 1
Row row = sheet.Rows.AddRow(RowIndex)
if (!dt.Columns.Contains(ColStruct.ColumnName))
{
continue
}
Cell cell = null
if (row.CellExists(ColumnIndex))
{
cell = row.GetCell(ColumnIndex)
}
else
{
cell = sheet.Cells.Add(RowIndex, ColumnIndex, null)
//row.AddCell(cell)
}
cell.Value = ColStruct.ColumnCaption
cell.Font.Weight = FontWeight.Bold
cell.HorizontalAlignment = HorizontalAlignments.Centered
cell.BottomLineStyle = 2
cell.BottomLineColor = Colors.Grey
cell.Font.Height = 10 * 20
cell.VerticalAlignment = VerticalAlignments.Centered
ushort ColumnMaxLength = GetColumnValueMaxLength(dt, ColStruct)
//设定列宽为自适应宽度
ColumnInfo colInfo = new ColumnInfo(xls, sheet)//生成列格式对象
//设定colInfo格式的起作用的列为第1列到第5列(列格式为0-base)
colInfo.ColumnIndexStart = (ushort)(ColumnIndex-1)
colInfo.ColumnIndexEnd = colInfo.ColumnIndexStart
colInfo.Width = (ushort)(ColumnMaxLength * 256)//列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo)//把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不能把colInfo对象多次附给sheet页）
ColumnIndex++
}
for (ushort i = 0i <dt.Rows.Counti++)
{
ushort RowIndex = (ushort)(i + 2)
Row row = sheet.Rows.AddRow(RowIndex)
int j = 0
foreach (ColumnStruct ColStruct in TableInfo.ColumnInfoAry)
{
if ( !dt.Columns.Contains(ColStruct.ColumnName) )
{
continue
}
ColumnIndex = (ushort)(j + 1)
Cell cell = null
if (row.CellExists(ColumnIndex))
{
cell = row.GetCell(ColumnIndex)
}
else
{
cell = sheet.Cells.Add(RowIndex, ColumnIndex, null)
//row.AddCell(cell)
}
object objValue = dt.Rows[i][ColStruct.ColumnName]
cell.Value = GetColumnValueFormat(dt,ColStruct,objValue)
cell.Font.Weight = FontWeight.Normal
cell.HorizontalAlignment = HorizontalAlignments.Centered
j++
}
}
}
xls.Send()
}
private static string GetColumnValueFormat(DataTable dt, ColumnStruct ColStruct, Object ObjValue)
{
string ColumnValue = string.Empty
if ( ObjValue != null &&ObjValue!= DBNull.Value )
{
string ColumnDataType = dt.Columns[ColStruct.ColumnName].DataType.ToString()
switch (ColumnDataType)
{
case "System.Boolean":
case "System.Byte":
{
ColumnValue = ObjValue.ToString()
break
}
case "System.Decimal":
{
if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
{
ColumnValue = Convert.ToDecimal(ObjValue).ToString()
}
else
{
ColumnValue = Convert.ToDecimal(ObjValue).ToString(ColStruct.ColumnTextFormat)
}
break
}
case "System.Double":
{
if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
{
ColumnValue = Convert.ToDouble(ObjValue).ToString()
}
else
{
ColumnValue = Convert.ToDouble(ObjValue).ToString(ColStruct.ColumnTextFormat)
}
break
}
case "System.Int64":
{
if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
{
ColumnValue = Convert.ToInt64(ObjValue).ToString()
}
else
{
ColumnValue = Convert.ToInt64(ObjValue).ToString(ColStruct.ColumnTextFormat)
}
break
}
case "System.Int16":
{
if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
{
ColumnValue = Convert.ToInt16(ObjValue).ToString()
}
else
{
ColumnValue = Convert.ToInt16(ObjValue).ToString(ColStruct.ColumnTextFormat)
}
break
}
case "System.Int32":
{
if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
{
ColumnValue = Convert.ToInt32(ObjValue).ToString()
}
else
{
ColumnValue = Convert.ToInt32(ObjValue).ToString(ColStruct.ColumnTextFormat)
}
break
}
case "System.DateTime":
{
if (string.IsNullOrEmpty(ColStruct.ColumnTextFormat))
{
ColumnValue = Convert.ToDateTime(ObjValue).ToString()
}
else
{
ColumnValue = Convert.ToDateTime(ObjValue).ToString(ColStruct.ColumnTextFormat,System.Globalization.DateTimeFormatInfo.InvariantInfo)
}
break
}
default:
{
ColumnValue = ObjValue.ToString()
break
}
}
}
return ColumnValue.Trim()
}
private static ushort GetColumnValueMaxLength(DataTable dt, ColumnStruct ColStruct)
{
ushort InitLenth = (ushort)System.Text.Encoding.Default.GetByteCount(ColStruct.ColumnCaption)
ushort MaxLenth = InitLenth
foreach (DataRow Row in dt.Rows)
{
object ObjValue = Row[ColStruct.ColumnName]
if (ObjValue == null || ObjValue == DBNull.Value)
{
continue
}
string ColumnValue = GetColumnValueFormat(dt,ColStruct,ObjValue)
ushort ColumnValueLenth = (ushort)System.Text.Encoding.Default.GetByteCount(ColumnValue)
MaxLenth = ColumnValueLenth >MaxLenth ? ColumnValueLenth : MaxLenth
}
if (MaxLenth == InitLenth)
{
//标题栏字体较大
MaxLenth += 4
}
else
{
//内容文本前后与边框空余出一字的间隔
MaxLenth += 2
}
return (ushort)(MaxLenth * 1.2)
}
}
页面调用ExcelHelper类中的静态方法ExportDataToExcel导出数据集中指定Table的制定列到Excel文件并输出文件到浏览器客户端。XmlManager.GetString是多语言实现的相关方法，可以直接修改为返回当前列标题字符串。
private void ExportDataToExcel(DSSummary_DetailsQuery_SellInfo ds)
{
System.Collections.Generic.List<ExcelHelper.TableStruct>TableInfoAry = new System.Collections.Generic.List<ExcelHelper.TableStruct>()
ExcelHelper.TableStruct TableInfo = new ExcelHelper.TableStruct()
TableInfo.TableName = ds.DS_Stock.TableName
TableInfo.TableCaption = XmlManager.GetString("Summary_DetailsQuery_ViewCarInfoCustormerDetail_TitleTxt")
ExcelHelper.ColumnStruct ColStruct = new ExcelHelper.ColumnStruct()
ColStruct.ColumnName = "C_CarNo"
ColStruct.ColumnTextFormat = string.Empty
ColStruct.ColumnCaption = XmlManager.GetString("IAMS_System_CarInfo_CarNo")
TableInfo.AddColumnInfo(ColStruct)
//……添加其他列信息
TableInfoAry.Add(TableInfo)
ExcelHelper.ExportToExcel(ds, TableInfoAry)
}
希望可以帮到你，有什么问题可以联系我。Email:wangminghu2000@163.com
大家好，我是开源电子表格Luckysheet的作者。Luckysheet的github仓库在5个月内获得了6K+ Star，获得码云GVP项目，已有腾讯、京东、华为、用友、微众银行等30+头部企业在用，社群服务600+开发者。
在社区的积极反馈下，我们吸取好的想法和建议，在模块化、工程化、文档完善、API增强、自定义配置等方面做了改进。非常感谢大家的关注。
这是之前的帖子： https://www.jianshu.com/p/4696b31e20c5
本次更新有两块重点内容
一是解决了公式性能问题。公式解析是Luckysheet的核心功能之一，旧版本在跨工作表解析公式的时候，原有的公式链有性能瓶颈，本次修改重构了公式链，渲染效率和公式执行效率得到大幅提升。
二是我们开源了协同编辑后端LuckysheetServer。有以下特点：
协同编辑演示 - http://luckysheet.lashuju.com/demo/
首要目标是将社区提出的Bug和需求优先解决掉，之后将会完善Excel导入导出，打印，树形菜单等增强功能，详细关注官方更新。
我们也有很多不足之处，欢迎大家批评反馈，帮助我们改进，希望能给大家带来更好的开发体验。
在服务器上安装iis；
打开iis，进入默认网站，右键单击，关闭iis服务；
将默认网站的路径指向公司的网站所在的目录，如对端口无要求，直接保存即可；
启动iis，即可浏览公司的网页。
IIS是Internet Information Services的缩写，是一个World Wide Web server。通过iis可以架设web服务器。
欢迎分享，转载请注明来源：夏雨云
微信扫一扫
支付宝扫一扫
评论列表（0条）