ExcelData

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyXls.SL2;
using org.in2bits.MyXls;
using System.Data;
using System.IO;
using System.Web;
using System.Collections;

 

public class ExcelData
{
public NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();

public ExcelData()
{ }

#region 下载导出文件--数据源DataTable
/// <summary>
/// 下载导出文件--数据源DataTable
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <param name="xlsname">文件名称</param>
/// <param name="table">数据集DataTable</param>
public void ExportExcelForWeb(string sheetName, string xlsname, DataTable table)
{

XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
try
{

if (table == null || table.Rows.Count == 0) { return; }
//XlsDocument xls = new XlsDocument();
//Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);

//填充表头
foreach (DataColumn col in table.Columns)
{
sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
}

//填充内容
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
}
}

//保存
//xls.FileName = xlsname;
//xls.Save();

#region 客户端保存
MemoryStreamSave(xls, sheet, xlsname);

#endregion
//xls = null;
}
catch (Exception ex)
{
log.Debug(ex.Message);
}
finally
{
sheet = null;
xls = null;
}

}
#endregion

#region 导出Excel,指定标题,按标题导出指定字段内容
/// <summary>
/// 导出Excel,指定标题,按标题导出指定字段内容
/// </summary>
/// <param name="table">导出数据源DataTable</param>
/// <param name="titleList">数据标题行</param>
public void ExportExcelForWeb(DataTable table, Dictionary<string,string> titleList)
{

XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet1");
try
{

if (table == null || table.Rows.Count == 0) { return; }

//填充表头
int n =1;
IDictionaryEnumerator Enum = titleList.GetEnumerator();
while (Enum.MoveNext())
{
sheet.Cells.Add(1, n, Enum.Value);
log.Debug(Enum.Key + " " + Enum.Value);
n++;
}

//填充内容
for (int i = 0; i < table.Rows.Count; i++)
{
int j = 1;
IDictionaryEnumerator col = titleList.GetEnumerator();
while (col.MoveNext())
{
sheet.Cells.Add(i + 2, j, table.Rows[i][col.Key.ToString()].ToString());
j++;
}
//for (int j = 0; j < titleList.Count; j++)
//{
// sheet.Cells.Add(i + 2, j + 1, table.Rows[i][titleList.GetKey(j).ToString()].ToString());
//}

}

//保存
//xls.FileName = xlsname;
//xls.Save();

#region 客户端保存
MemoryStreamSave(xls, sheet);
#endregion
//xls = null;
}
catch (Exception ex)
{
log.Debug(ex.Message);
}
finally
{
sheet = null;
xls = null;
}

}
#endregion

 

#region 导出Excel,指定标题
/// <summary>
/// 导出Excel,指定标题
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <param name="xlsname">excel文件名称</param>
/// <param name="table">导出数据源DataTable</param>
/// <param name="titleList">数据标题行</param>
public void ExportExcelForWeb(string sheetName, string xlsname, DataTable table, Dictionary<string, string> titleList)
{

XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
try
{

if (table == null || table.Rows.Count == 0) { return; }
//XlsDocument xls = new XlsDocument();
//Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);

//填充表头
foreach (DataColumn col in table.Columns)
{
string name = col.ColumnName.Trim();
object namestr = (object)name;
IDictionaryEnumerator Enum = titleList.GetEnumerator();
while (Enum.MoveNext())
{
if (Enum.Key.ToString().Trim() == name)
{
namestr = Enum.Value;
}
}
sheet.Cells.Add(1, col.Ordinal + 1, namestr);
}

//填充内容
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
}
}

//保存
//xls.FileName = xlsname;
//xls.Save();

#region 客户端保存
using (MemoryStream ms = new MemoryStream())
{
xls.Save(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
xls = null;
HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();

response.Charset = "UTF-8";
response.ContentType = "application/vnd-excel";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + xlsname));
//System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
byte[] data = ms.ToArray();
System.Web.HttpContext.Current.Response.BinaryWrite(data);

}

#endregion
//xls = null;
}
catch (Exception ex)
{
log.Debug(ex.Message);
}
finally
{
sheet = null;
xls = null;
}

}
#endregion

#region DataSet生成XLS报表
/// <summary>
/// DataSet生成XLS报表
/// </summary>
/// <param name="ds">获取DataSet数据集</param>
/// <param name="xlsName">报表表名</param>
private void xlsGridview(DataSet ds, string xlsName)
{
XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
try
{
int rowIndex = 1;
int colIndex = 0;

System.Data.DataTable table = ds.Tables[0];

Cells cells = sheet.Cells;
foreach (DataColumn col in table.Columns)
{
colIndex++;
//sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行
cells.Add(1, colIndex, col.ColumnName);
}

foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
//sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
Cell cell = cells.Add(rowIndex, colIndex, Convert.ToDouble(row[col.ColumnName].ToString()));//转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = true; //字体为粗体
}
}
}
catch (Exception ex)
{
log.Debug(ex.Message);
}
finally
{
xls = null;
sheet = null;
}
}
#endregion

#region 生成导出数据,客户端保存
/// <summary>
/// 生成导出数据,客户端保存,默认文件名称
/// </summary>
/// <param name="xls">XlsDocument数据,</param>
/// <param name="sheet">Worksheet</param>
public void MemoryStreamSave(XlsDocument xls, Worksheet sheet)
{
using (MemoryStream ms = new MemoryStream())
{
xls.Save(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
xls = null;
HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();

response.Charset = "UTF-8";
response.ContentType = "application/vnd-excel";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond + ".xls"));
//System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
byte[] data = ms.ToArray();
System.Web.HttpContext.Current.Response.BinaryWrite(data);

}
}
/// <summary>
/// 生成导出数据,客户端保存
/// </summary>
/// <param name="xls"></param>
/// <param name="sheet"></param>
/// <param name="fileName"></param>
public void MemoryStreamSave(XlsDocument xls, Worksheet sheet, string fileName)
{
using (MemoryStream ms = new MemoryStream())
{
xls.Save(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
xls = null;
HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();

response.Charset = "UTF-8";
response.ContentType = "application/vnd-excel";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + fileName + ".xls"));
//System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
byte[] data = ms.ToArray();
System.Web.HttpContext.Current.Response.BinaryWrite(data);

}
}
#endregion

}