【转】（C#）用MyXls生成Excel报表
写在前面的话：前面的Excel二进制流的方式导出excel,每次打开都提示"您尝试打开的文件“文件名.xls”的格式与文件扩展名指定的格式不一致"，让人用着很不爽。经查找是在导出的时候生成是伪EXCEL,说白了就是在excel里嵌入了HTML代码，真正的excel导出以后,用txt打开是乱码的。
开始正文：
MyXls是用C#开源项目，可以应用于asp.net 或者 .net应用程序上。它根据微软公开的Excle文档格式文件（BIFF），以二进制格式直接生成excel文档，支持Excel versions 97 - 2007. 。这意味着可以不用在服务器上安装office就能够以excle格式输出数据库中存储的数据了。这对于许多项目来说都是很有用的。
目前MyXls已经实现了单元格（cell）的格式设置，包括文本颜色、文本大小、字体、单位格边框、底色、列宽、行高，合并单元格，多个sheet页等功能。
目前MyXls还不支持在excel文档中生成对象（如、文本框、按钮等）。MyXls主页称即将实现对excel文件的读取功能，个人认为读取的功能的用处还不是很多
使用很方便，下载org.in2bits.MyXls.dll，
引入命名空间即可
using org.in2bits.MyXls;
using org.in2bits.MyXls.ByteUtil;
下面是一个使用代码示例：
private void ExcelExport(IList< LineLossInfo > lossListExcel)
{
XlsDocument xls = new XlsDocument();
xls.FileName = "LineLossMonthDetail.xls";//指定文件名
Worksheet sheet = xls.Workbook.Worksheets.Add("LineLossMonthDetail");
#region 设置各数据列的大小
ColumnInfo colInfo1 = new ColumnInfo(xls, sheet);
colInfo1.ColumnIndexStart = 0;
colInfo1.ColumnIndexEnd = 1;
colInfo1.Width = 15 * 256;
sheet.AddColumnInfo(colInfo1);
ColumnInfo colInfo2 = new ColumnInfo(xls, sheet);
colInfo2.ColumnIndexStart = 2;
colInfo2.ColumnIndexEnd = 2;
colInfo2.Width = 15 * 256;
sheet.AddColumnInfo(colInfo2);
ColumnInfo colInfo3 = new ColumnInfo(xls, sheet);
colInfo3.ColumnIndexStart = 3;
colInfo3.ColumnIndexEnd = 3;
colInfo3.Width = 15 * 256;
sheet.AddColumnInfo(colInfo3);
ColumnInfo colInfo4 = new ColumnInfo(xls, sheet);
colInfo4.ColumnIndexStart = 4;
colInfo4.ColumnIndexEnd = 4;
colInfo4.Width = 15 * 256;
sheet.AddColumnInfo(colInfo4);
ColumnInfo colInfo5 = new ColumnInfo(xls, sheet);
colInfo5.ColumnIndexStart = 5;
colInfo5.ColumnIndexEnd = 5;
colInfo5.Width = 32 * 256;
sheet.AddColumnInfo(colInfo5);
#endregion
Cells cells = sheet.Cells;
#region 合并单元格，得到报表标题
MergeArea maTitle = new MergeArea(1, 2, 1, 6);
sheet.AddMergeArea(maTitle);
XF xfTitle = xls.NewXF();
xfTitle.HorizontalAlignment = HorizontalAlignments.Centered;
xfTitle.VerticalAlignment = VerticalAlignments.Centered;
xfTitle.Font.FontName = "宋体";
xfTitle.Font.Height = 16 * 20;
xfTitle.Font.Bold = true;
cells.Add(1, 1, divDataHeader.InnerText, xfTitle);
#endregion
MergeArea maTime1 = new MergeArea(3, 3, 1, 6);
sheet.AddMergeArea(maTime1);
XF xfTopBar = xls.NewXF();
xfTopBar.Font.FontName = "宋体";
cells.Add(3, 1, divDataCondition.InnerText, xfTopBar);
#region 设置Excel数据列标题的格式
XF xfDataHead = xls.NewXF();
xfDataHead.HorizontalAlignment = HorizontalAlignments.Centered;
xfDataHead.VerticalAlignment = VerticalAlignments.Centered;
xfDataHead.Font.FontName = "宋体";
xfDataHead.Font.Bold = true;
xfDataHead.UseBorder = true;
xfDataHead.BottomLineStyle = 1;
xfDataHead.BottomLineColor = Colors.Black;
xfDataHead.TopLineStyle = 1;
xfDataHead.TopLineColor = Colors.Black;
xfDataHead.LeftLineStyle = 1;
xfDataHead.LeftLineColor = Colors.Black;
xfDataHead.RightLineStyle = 1;
xfDataHead.RightLineColor = Colors.Black;
#endregion
#region 添加列标题
cells.Add(4, 1, "日期", xfDataHead);
cells.Add(4, 2, "供电量", xfDataHead);
cells.Add(4, 3, "用电量", xfDataHead);
cells.Add(4, 4, "损失量", xfDataHead);
cells.Add(4, 5, "损失率", xfDataHead);
cells.Add(4, 6, "说明", xfDataHead);
#endregion
#region 设置各数据列的格式
XF xfData = xls.NewXF();
xfData.Font.FontName = "宋体";
xfData.UseBorder = true;
xfData.BottomLineStyle = 1;
xfData.BottomLineColor = Colors.Black;
xfData.TopLineStyle = 1;
xfData.TopLineColor = Colors.Black;
xfData.LeftLineStyle = 1;
xfData.LeftLineColor = Colors.Black;
xfData.RightLineStyle = 1;
xfData.RightLineColor = Colors.Black;
#endregion
#region 填充数据
int i = 5;//从第五行开始为数据行
double provideTotal = 0;
double useTotal = 0;
double lossTotal = 0;
foreach(LineLossInfo lossItem in lossListExcel)
{
cells.Add(i,1,lossItem.Date,xfData);
if (Double.IsNaN(lossItem.ProvideValue))
cells.Add(i, 2, "-", xfData);
else
{
cells.Add(i, 2, lossItem.ProvideValue, xfData);
provideTotal += lossItem.ProvideValue;
}
if (Double.IsNaN(lossItem.UseValue))
cells.Add(i, 3, "-", xfData);
else
{
cells.Add(i, 3, lossItem.UseValue, xfData);
useTotal += lossItem.UseValue;
}
if (Double.IsNaN(lossItem.LossValue))
cells.Add(i, 4, "-", xfData);
else
{
cells.Add(i, 4, lossItem.LossValue, xfData);
lossTotal += lossItem.LossValue;
}
if(Double.IsNaN(lossItem.LossRate))
cells.Add(i,5,"-",xfData);
else
cells.Add(i,5,lossItem.LossRate,xfData);
if(String.IsNullOrEmpty(lossItem.Info))
cells.Add(i,6,"-",xfData);
else
cells.Add(i,6,lossItem.Info,xfData);
i++;
}
//添加总计
cells.Add(i, 2, "总计：", xfData);
cells.Add(i, 3, provideTotal, xfData);
cells.Add(i, 4, useTotal, xfData);
cells.Add(i, 5, lossTotal, xfData);
#endregion
//发送到客户端
xls.Send();
}
