ExcelHelper office 导出
要是服务器上没有装着excel 可以用c#导出excel表吗
租用的空间 服务器上没有装着office excel，可以用c#导出excel表吗？
怎样做？给点代码……
可以使用二进制读写Excel的开源组件org.in2bits.MyXls.dll导出真正的Excel文件。 以下是我简单封装的一个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 = 0; i < dt.Rows.Count; i++) { 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
