1、引用:NPOI.dll 文件
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。
NPOI使用手册:https://pan.baidu.com/s/1c1QjDba
2、导出Excel
调用方法:
DataTableToExcel.ExportByWeb(dt, null, true, "test.xls");
具体实现代码:
public class DataTableToExcel
{
#region 导出excel
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
private static MemoryStream Export(DataTable dtSource)
{
//1
HSSFWorkbook workbook = new HSSFWorkbook();//创建一个新的excel
ISheet sheet = workbook.CreateSheet();//创建sheet页
//2
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "zrf";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "zrf"; //填加xls文件作者信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
//3 设置样式
ICellStyle dateStyle = workbook.CreateCellStyle();// Sheet样式
dateStyle.Alignment = HorizontalAlignment.Center;//设置单元格的样式:水平对齐居中
dateStyle.VerticalAlignment = VerticalAlignment.Center;//设置单元格样式:垂直对齐居中
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//4 获取字符数
int[] arrColWidth = new int[dtSource.Columns.Count];//取得列宽 标题 占字符数
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
//单元格内容占的字符数大于 标题 占字符数 就修改 标题数组的值
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
//5 循环遍历DataTable 往Excel sheet页面 赋值
int rowIndex = 0;
int count = 0;
foreach (DataRow row in dtSource.Rows)
{
//6
#region 新建表,填充表头,填充列头,样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(0); //设置第一行为Header
headerRow.HeightInPoints = 15;
//样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 7 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);//创建数据行
dataRow.HeightInPoints = 15;
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);//创建单元格
string drValue = row[column].ToString();
newCell.CellStyle = dateStyle;
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);//单元格赋值
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);//单元格赋值
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);//单元格赋值
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);//单元格赋值
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);//单元格赋值
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");//单元格赋值
break;
}
}
#endregion
//8、合并行
DataRow[] drrr = dtSource.Select("班级='" + row["班级"] + "'");
if (drrr != null && rowIndex >= count)
{
count = drrr.Count()+ rowIndex;
//CellRangeAddress四个参数:起始行、结束行、起始列、结束列
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, count-1, 0, 0));
}
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
/// <summary>
/// web页面导出Excel
/// </summary>
/// <param name="dtSource">源数据</param>
/// <param name="TableHead">Excel文件的表头和源数据的表头对应关系表</param>
/// <param name="displayOther">是否导出没有在对应关系表中的列,false不导出,true导出</param>
/// <param name="strFileName">导出的excel文件名字</param>
public static void ExportByWeb(DataTable dtSource, List<DictionaryEntry> TableHead, bool displayOther, string strFileName)
{
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "UTF-8";
if (string.IsNullOrEmpty(strFileName))
strFileName = DateTime.Now.ToString("yyyyMMddHHmmssffff.xls");
#region
if (curContext.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
{
curContext.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", strFileName));
}
else
{
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
}
#endregion
if (TableHead == null)
{
TableHead = new List<DictionaryEntry>();
}
DataTable dt = GetTable(dtSource, TableHead, displayOther);
Byte[] bytes = Export(dt).GetBuffer();
curContext.Response.BinaryWrite(bytes);
curContext.Response.End();
}
/// <summary>
///
/// </summary>
/// <param name="dtSource"></param>
/// <param name="TableHead"></param>
/// <param name="displayOther"></param>
/// <returns></returns>
private static DataTable GetTable(DataTable dtSource, List<DictionaryEntry> TableHead, bool displayOther)
{
if (displayOther)
{
foreach (DataColumn col in dtSource.Columns)
{
foreach (DictionaryEntry dic in TableHead)
{
if (dic.Key.ToString() == col.ColumnName)
{
col.ColumnName = dic.Value.ToString();
break;
}
}
}
return dtSource;
}
else
{
DataTable dt = new DataTable();
foreach (DictionaryEntry dic in TableHead)
{
dt.Columns.Add(dic.Value.ToString());
}
foreach (DataRow dr in dtSource.Rows)
{
DataRow newRow = dt.NewRow();
foreach (DictionaryEntry dic in TableHead)
{
newRow[dic.Value.ToString()] = dr[dic.Key.ToString()];
}
dt.Rows.Add(newRow);
}
return dt;
}
}
#endregion
}