作者：乔山办公网日期：
返回目录：office365
下载“AccessDatabaseEngine.exe”装上就可以了，不用装office软件
可以使用二进制读写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);
//……添加其e799bee5baa6e79fa5e98193e78988e69d83361他列信息
TableInfoAry.Add(TableInfo);
ExcelHelper.ExportToExcel(ds, TableInfoAry);
}
希望可以帮到你，有什么问题可以联系我。Email:
1.打开 控制面板；
2.在添加或删除程序中找到 Microsoft Office Professional Edition 2007 点击 更改；
在弹出的窗口中选择 添加或删除功能 点击 下一步;
在自定义安装界面将 excel 前面的勾取消勾选，点击 更新；
3.更新完成后，请7a64e59b9ee7ad94363点击 开始-运行-regedit 点击 确定 进入注册表，并删除注册表中的如下项：HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\excel
HKEY_CURRENT_USER\Software\Microsoft\Office\excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0\excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\excel
（如果没有找到对应的项，请跳过即可）
注册表最后一级可以是Excel 或是Word、powerpoint。
4.以上操作完成后请重新打开 添加或删除程序，并找到 Microsoft Office Professional Edition 2007 点击 更改，下一步，在自定义安装界面将 excel 前面的勾勾选，再点击 更新 即可。
下载“AccessDatabaseEngine.exe”装上就可以了，不用装office软件