using Aspose.Cells;
using Newtonsoft.Json;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Web;
namespace Kingo.Proof.Service.Common
{
public class ExcelHelper : IDisposable
{
private string fileName = null; //文件名
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper() { }
public ExcelHelper(string fileName)
{
this.fileName = fileName;
disposed = false;
}
private int TitleSize;
public ExcelHelper(int titleSize)
{
TitleSize = titleSize;
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, int startRow = 0)
{
ISheet sheet = null;
DataTable data = new DataTable();
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null && sheetName != "")
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(startRow);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.Value().ToStringWDV();
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
if (startRow == 0)
{
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow += 1;
}
}
else
{
if (startRow == 0)
{
startRow = sheet.FirstRowNum;
}
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0 || (string.IsNullOrWhiteSpace(row.GetCell(0).Value().ToStringWDV()) && string.IsNullOrWhiteSpace(row.GetCell(1).Value().ToStringWDV()) && string.IsNullOrWhiteSpace(row.GetCell(2).Value().ToStringWDV()))) break; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).Value().ToStringWDV();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
/// <summary>
/// 将excel中多sheet表的数据导入到DataTable中
/// </summary>
/// <param name="sheetnumber">excel工作薄sheet的位置</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(int sheetnumber, bool isFirstRowColumn, int startRow = 0)
{
ISheet sheet = null;
DataTable data = new DataTable();
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheetAt(sheetnumber);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(startRow);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.Value().ToStringWDV();
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
if (startRow == 0)
{
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow += 1;
}
}
else
{
if (startRow == 0)
{
startRow = sheet.FirstRowNum;
}
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0 || (string.IsNullOrWhiteSpace(row.GetCell(0).Value().ToStringWDV()) && string.IsNullOrWhiteSpace(row.GetCell(1).Value().ToStringWDV()) && string.IsNullOrWhiteSpace(row.GetCell(2).Value().ToStringWDV()))) break; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).Value().ToStringWDV();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return data;
}
}
public List<datamodel> ExcelToData(int sheetnumber, bool isFirstRowColumn, int startRow = 0)
{
ISheet sheet = null;
List<datamodel> dataList = new List<datamodel>();
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheetAt(sheetnumber);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(startRow);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
//sheet表名,第一行
string sheetname = sheet.GetRow(0).GetCell(0).Value().ToStringWDV();
//sheet小标题表名，第二行
string titlename = "";
List<titlesheet> titlelist = new List<titlesheet>() { };
IRow rowtitle = sheet.GetRow(1);
for (int i = rowtitle.FirstCellNum; i < cellCount; ++i)
{
ICell cell = rowtitle.GetCell(i);
if (cell != null)
{
string cellValue = cell.Value().ToStringWDV();
if (!string.IsNullOrEmpty(cellValue))
{
if (!string.IsNullOrEmpty(titlename))
{
titlesheet titlemodel = new titlesheet();
titlemodel.title = titlename;
titlemodel.num = i;
titlelist.Add(titlemodel);
}
titlename = cellValue;
}
}
}
if (cellCount > 0)
{
titlesheet titlemodel = new titlesheet();
titlemodel.title = titlename;
titlemodel.num = cellCount;
titlelist.Add(titlemodel);
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int k = 0; k < titlelist.Count; k++)
{
//开始第一列
int firstcol = 0;
if (k != 0)
{
firstcol = titlelist[k - 1].num;
}
//最后一列
int lastcol = titlelist[k].num;
//分类建表单
DataTable data = new DataTable();
//创建表名
for (int j = firstcol; j < lastcol; ++j)
{
DataColumn column = new DataColumn(sheet.GetRow(startRow).GetCell(j).Value().ToStringWDV());
data.Columns.Add(column);
}
for (int i = startRow + 1; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0 || (string.IsNullOrWhiteSpace(row.GetCell(0).Value().ToStringWDV()) && string.IsNullOrWhiteSpace(row.GetCell(1).Value().ToStringWDV()) && string.IsNullOrWhiteSpace(row.GetCell(2).Value().ToStringWDV()))) break; //没有数据的行默认是null
if (firstcol == 0)
{
datamodel _datamodel = new datamodel();
_datamodel.xmbh = row.GetCell(0).Value().ToStringWDV();
_datamodel.datajson = "";
dataList.Add(_datamodel);
} //插入行数据
DataRow dataRow = data.NewRow();
for (int j = firstcol; j < lastcol; ++j)
{
if (row.GetCell(j) != null)
{ //同理，没有数据的单元格都默认是null
dataRow[j - firstcol] = row.GetCell(j).Value().ToStringWDV();
}
}
data.Rows.Add(dataRow);
}
dataList = datajson(data, dataList, titlelist[k].title, k, titlelist.Count);
}
foreach (datamodel item in dataList)
{
item.datajson = "{\"name\":\"" + sheetname + "\",\"value\":[" + item.datajson + "]}";
}
}
return dataList;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return dataList;
}
}
/// <summary>
/// 数据json化
/// </summary>
/// <param name="dt">各级菜单序列化</param>
/// <param name="_list">每个项目对应datajson</param>
/// <param name="title">二级标题名称</param>
/// <returns></returns>
public List<datamodel> datajson(DataTable dt, List<datamodel> _list, string title, int k, int count)
{
List<Dictionary<string, string>> dicDataJsonList = new List<Dictionary<string, string>>();
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Dictionary<string, string> dicDataJson = new Dictionary<string, string>();
dicDataJson.Add("name", dt.Columns[j].ColumnName);
dicDataJson.Add("value", dt.Rows[i][dt.Columns[j].ColumnName].ToString());
dicDataJsonList.Add(dicDataJson);
}
_list[i].datajson += "{\"name\":\"" + title + "\",\"value\":" + JsonConvert.SerializeObject(dicDataJsonList) + "}";
if (k + 1 != count)
{
_list[i].datajson += ",";
}
dicDataJsonList.Clear();
}
return _list;
}
public class datamodel
{
public string xmbh { get; set; }
public string datajson { get; set; }
}
public class titlesheet
{
public string title { get; set; }
public int num { get; set; }
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.Close();
}
fs = null;
disposed = true;
}
}
#region 使用elementui多表头同种数据格式导出Excel
List<ExcelFormat> noChildrenFormats => totalFormats.Where(x => x.children == null || x.children.Count <= 0).ToList();
Style contentStyle;
Style titleStyle;
bool hasSubTitle;
List<ExcelFormat> totalFormats;
int minLevel => totalFormats.Min(x => x.level);
int maxLevel => totalFormats.Max(x => x.level);
void InitParams(List<ExcelFormat> formats)
{
totalFormats = new List<ExcelFormat>();
GetTitleFormats(formats);
CreateCellsStyle();
}
void CreateCellsStyle()
{
contentStyle = new Style();
contentStyle.Font.Name = "Microsoft YaHei";
contentStyle.Font.Size = 10;
contentStyle.Font.IsBold = true;
contentStyle.HorizontalAlignment = TextAlignmentType.Center;
contentStyle.VerticalAlignment = TextAlignmentType.Center;
contentStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
contentStyle.Borders[BorderType.TopBorder].Color = Color.Black;
contentStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
contentStyle.Borders[BorderType.BottomBorder].Color = Color.Black;
contentStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
contentStyle.Borders[BorderType.LeftBorder].Color = Color.Black;
contentStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
contentStyle.Borders[BorderType.RightBorder].Color = Color.Black;
titleStyle = new Style();
titleStyle.Font.Name = "Microsoft YaHei";
titleStyle.Font.IsBold = true;
titleStyle.Font.Size = TitleSize == 0 ? 28 : TitleSize;
titleStyle.HorizontalAlignment = TextAlignmentType.Center;
}
void GetTitleFormats(List<ExcelFormat> formats)
{
foreach (ExcelFormat item in formats)
{
totalFormats.Add(item);
if (item.children != null && item.children.Count > 0)
{
GetTitleFormats(item.children);
}
}
}
/// <summary>
/// 通过formatter制作表头
/// </summary>
/// <param name="formats"></param>
/// <returns></returns>
private void CreateHeader(Cells cells)
{
List<ExcelFormat> firstLevelFormats = totalFormats.Where(x => x.level == minLevel).ToList();
int currentCol = 0;
foreach (ExcelFormat first in firstLevelFormats)
{
currentCol += RecursiveCreateHeader(cells, first, currentCol);
}
}
void GetWithChildrenFormatsCnt(ExcelFormat format, ref int totalBeforeCols)
{
if (format.children != null && format.children.Count > 0)
{
foreach (var item in format.children)
{
GetWithChildrenFormatsCnt(item, ref totalBeforeCols);
}
}
else
{
totalBeforeCols++;
}
}
/// <summary>
/// 递归创建表头
/// </summary>
/// <param name="cells"></param>
/// <param name="format"></param>
/// <param name="startCol"></param>
/// <returns></returns>
private int RecursiveCreateHeader(Cells cells, ExcelFormat format, int startCol)
{
int totalChildrenCnt = 0;
GetWithChildrenFormatsCnt(format, ref totalChildrenCnt);
int currentRowIndex = hasSubTitle ? format.level + 1 : format.level;
//设置当前行的跨行和内容
cells[currentRowIndex, startCol].PutValue(format.label);
if (totalChildrenCnt > 1)
cells.Merge(currentRowIndex, startCol, 1, totalChildrenCnt);
if (format.children == null || format.children.Count <= 0)
{
int mergeRows = maxLevel - format.level + 1;
if (mergeRows > 1)
cells.Merge(currentRowIndex, startCol, mergeRows, 1);
}
else
{
int currentCol = startCol;
foreach (ExcelFormat child in format.children)
{
currentCol += RecursiveCreateHeader(cells, child, currentCol);
}
}
return totalChildrenCnt;
}
public static DataTable AsposeExcelToDataTable(int firstRow, int firstColumn, string fileName)
{
Workbook workbook = new Workbook(fileName);
DataTable dtExcel = null;
try
{
Cells cells = workbook.Worksheets[0].Cells;
dtExcel = cells.ExportDataTableAsString(firstRow, firstColumn, cells.MaxDataRow + 1, cells.MaxColumn + 1, true);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return dtExcel;
}
public MemoryStream DtToExcelWithAspose(DataTable dt, List<ExcelFormat> formats, string title, string subTitle)
{
if (dt == null || dt.Rows.Count <= 0 || dt.Columns.Count <= 0) return null;
try
{
//SetAsposeLicence();
Workbook workbook = new Workbook();
Worksheet cellSheet = workbook.Worksheets[0];
Cells cells = cellSheet.Cells;
InitParams(formats);
hasSubTitle = !string.IsNullOrEmpty(subTitle);
//通过formatter制作表头
//headerRowsCnt = 0;//将统计行数量置为0
CreateHeader(cells);
//制作标题
cells[0, 0].PutValue(title);
//合并单元格cells.Merge(1, 0, 3, 1) 参数1代表当前行，参数0代表当前行当前列即第一行第一列，参数3合并的行数，参数4合并的列数
cells.Merge(0, 0, 1, noChildrenFormats.Count);
cells[0, 0].SetStyle(titleStyle);
//设置副标题
cells[1, noChildrenFormats.Count - 2].PutValue(subTitle);
int rowIndex = hasSubTitle ? maxLevel + 2 : maxLevel + 1;//总共行数+title一行-索引0开始 的1 + 下一行1
int colIndex = 0;
int colCount = noChildrenFormats.Count;
int rowCount = dt.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
try
{
//通过prop获取 集合数据属性
object o = dt.Rows[i][noChildrenFormats[j].prop];
string content = o == null ? "" : o.ToString();
cellSheet.Cells[rowIndex, colIndex].PutValue(content);
}
catch (Exception ex)
{
cellSheet.Cells[rowIndex, colIndex].PutValue("填充异常:" + ex.Message);
}
colIndex++;
}
rowIndex++;
}
//水平垂直居中
cellSheet.AutoFitColumns();
cellSheet.AutoFitRows();
//cellSheet.Name = title;
//内容字体,边框,居中
Aspose.Cells.Range rangeBorder = cellSheet.Cells.CreateRange(hasSubTitle ? 2 : 1, 0, rowCount + maxLevel, colCount);
StyleFlag flag = new StyleFlag() { Borders = true, FontSize = true, FontName = true, VerticalAlignment = true, HorizontalAlignment = true };
rangeBorder.ApplyStyle(contentStyle, flag);
//header内容加粗
Aspose.Cells.Range rangeHeader = cellSheet.Cells.CreateRange(hasSubTitle ? 2 : 1, 0, maxLevel, colCount);
flag = new StyleFlag() { FontBold = true };
rangeHeader.ApplyStyle(contentStyle, flag);
MemoryStream ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Xlsx);
return ms;
}
catch (Exception ex)
{
throw ex;
}
}
public MemoryStream ObjectToExcelWithAspose<T>(List<T> ts, List<ExcelFormat> formats, string title, string subTitle)
{
if (ts == null || ts.Count <= 0 || typeof(T).GetProperties().Count() <= 0) return null;
try
{
//SetAsposeLicence();
Workbook workbook = new Workbook();
Worksheet cellSheet = workbook.Worksheets[0];
Cells cells = cellSheet.Cells;
InitParams(formats);
hasSubTitle = !string.IsNullOrEmpty(subTitle);
//通过formatter制作表头
//headerRowsCnt = 0;//将统计行数量置为0
CreateHeader(cells);
//制作标题
cells[0, 0].PutValue(title);
//合并单元格cells.Merge(1, 0, 3, 1) 参数1代表当前行，参数0代表当前行当前列即第一行第一列，参数3合并的行数，参数4合并的列数
cells.Merge(0, 0, 1, noChildrenFormats.Count);
cells[0, 0].SetStyle(titleStyle);
//设置副标题
if (hasSubTitle)
cells[1, noChildrenFormats.Count - 2].PutValue(subTitle);
int rowIndex = hasSubTitle ? maxLevel + 2 : maxLevel + 1;//总共行数+title一行-索引0开始 的1 + 下一行1
int colIndex = 0;
int colCount = noChildrenFormats.Count;
int rowCount = ts.Count;
Type type = typeof(T);
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
try
{
//通过prop获取 集合数据属性
object o = type.GetProperty(noChildrenFormats[j].prop).GetValue(ts[i], null);
string content = o == null ? "" : o.ToString();
cellSheet.Cells[rowIndex, colIndex].PutValue(content);
}
catch (Exception ex)
{
cellSheet.Cells[rowIndex, colIndex].PutValue("填充异常:" + ex.Message);
}
colIndex++;
}
rowIndex++;
}
//水平垂直居中
cellSheet.AutoFitColumns();
cellSheet.AutoFitRows();
//cellSheet.Name = title;
//内容字体,边框,居中
Aspose.Cells.Range rangeBorder = cellSheet.Cells.CreateRange(hasSubTitle ? 2 : 1, 0, rowCount + maxLevel, colCount);
StyleFlag flag = new StyleFlag() { Borders = true, FontSize = true, FontName = true, VerticalAlignment = true, HorizontalAlignment = true };
rangeBorder.ApplyStyle(contentStyle, flag);
//header内容加粗
Aspose.Cells.Range rangeHeader = cellSheet.Cells.CreateRange(hasSubTitle ? 2 : 1, 0, maxLevel, colCount);
flag = new StyleFlag() { FontBold = true };
rangeHeader.ApplyStyle(contentStyle, flag);
MemoryStream ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Xlsx);
return ms;
}
catch (Exception ex)
{
throw ex;
}
}
public void DownFile(MemoryStream filestream, string fileName)
{
var response = Kingo.Common.HttpContext.Current.Response;
try
{
if (filestream == null)
{
response.StatusCode = (int)HttpStatusCode.NoContent;
return;// API.HttpResponse(204, "未查询到数据");
}
//byte[] buff = new byte[filestream.Capacity];
//if (filestream.CanRead)
//{
// filestream.Read(buff, 0, filestream.Capacity);
//}
//HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK)
//{
// Content = new ByteArrayContent(buff, 0, (int)filestream.Length)
//};
//response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd-ms-excel");
//response.Content.Headers.ContentLength = filestream.Length;
增加文件名参数
//response.Headers.Add("Access-Control-Expose-Headers", "fileName");
//response.Headers.Add("fileName", HttpUtility.UrlEncode(fileName));
//filestream.Close();
response.ContentType = "application/vnd-ms-excel";
response.Headers.Add("Access-Control-Expose-Headers", "fileName");
response.Headers.Add("fileName", HttpUtility.UrlEncode(fileName));
response.Headers.Add("Access-Control-Allow-Origin", "*");
var buffer = filestream.GetBuffer();
response.ContentLength = buffer.Length;
response.StatusCode = (int)HttpStatusCode.OK;
filestream.Position = 0;
response.Body.Write(buffer);
filestream.Close();
//return response;
}
catch (Exception ex)
{
response.StatusCode = (int)HttpStatusCode.NoContent;
return;// new HttpResponseMessage(HttpStatusCode.NoContent);
}
}
#endregion
#region NPOI导出Excel
/// <summary>
/// DataTable导出成Excel
/// </summary>
/// <param name="filePath">文件绝对路径(包括文件名与扩展名)</param>
/// <param name="dt">DataTable</param>
/// <param name="startRowIndex">从第startRowIndex行开始写入</param>
public static MemoryStream ExportToExcel(string filePath, DataTable dt, int startRowIndex)
{
if (string.IsNullOrWhiteSpace(filePath))
{
throw new Exception("文件的绝对路径不能为空！");
}
MemoryStreamWrap ms = new MemoryStreamWrap();
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
try
{
IWorkbook workbook = GetWorkbook(fs);
if (workbook == null)
{
throw new Exception(string.Format("文件扩展名异常，不是xlsx和xls。（fileName={0}）", Path.GetFileName(filePath)));
}
ISheet sheet = workbook.GetSheetAt(0);
if (dt != null)
{
//渲染Sheet表
RenderSheet(dt, workbook, sheet, startRowIndex);
}
ms.CanClose = false;
workbook.Write(ms);
ms.CanClose = true;
ms.Position = 0;
}
catch (Exception ex)
{
Common.LogAPI.Debug(ex);
}
}
return ms;
}
/// <summary>
/// DataSet导出成Excel
/// </summary>
/// <param name="filePath">文件绝对路径(包括文件名与扩展名)</param>
/// <param name="ds">DataSet</param>
public static MemoryStream ExportToExcel(String filePath, DataSet ds, List<int> startRowIndexList)
{
if (string.IsNullOrWhiteSpace(filePath))
{
throw new Exception("文件的绝对路径不能为空！");
}
MemoryStreamWrap ms = new MemoryStreamWrap();
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = GetWorkbook(fs);
if (workbook == null)
{
throw new Exception(string.Format("文件扩展名异常，不是xlsx和xls。（fileName={0}）", Path.GetFileName(filePath)));
}
if (ds != null && ds.Tables != null)
{
for (int i = 0; i < ds.Tables.Count; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
if (ds.Tables[i] != null)
{
RenderSheet(ds.Tables[i], workbook, sheet, startRowIndexList[i]);
}
}
}
ms.CanClose = false;
workbook.Write(ms);
ms.CanClose = true;
ms.Position = 0;
}
return ms;
}
/// <summary>
/// 渲染Sheet表
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="workbook">工作簿</param>
/// <param name="sheet">Sheet对象</param>
/// <param name="tableTitle">表的标题</param>
/// <param name="startRowIndex">从第startRowIndex行开始写入数据</param>
private static void RenderSheet(DataTable dt, IWorkbook workbook, ISheet sheet, int startRowIndex)
{
if (dt != null)
{
//一张Excel工作表，对于是office excel 2003及以下版本最大行数为65536行，最大列数256列；对于是office excel 2007及以上版本最大行数为1048576行，最大列数为16384列。
//if (dt.Columns.Count > 255)
//{
// throw new Exception("列数超过256列！");
//}
//if (dt.Rows.Count > 65536)
//{
// throw new Exception("行数超过65536行！");
//}
IRow row = null;
//列开始的行号
int rowIndex = startRowIndex;
for (int i = 0; i < dt.Rows.Count; i++)
{
#region 处理行数大于65536行
//if (((i == 0) && (fileExt == ".xlsx")) || ((i % 65535 == 0) && (fileExt == ".xls")))
if ((i % 65535 == 0) && (i > 0))
{
rowIndex = 0;
string sheetName = string.Format("{0}{1}", sheet.SheetName, i / 65535);
sheet = workbook.CreateSheet(sheetName);
#region 表格列头
row = sheet.CreateRow(rowIndex);
for (int col = 0; col < dt.Columns.Count; col++)
{
row.CreateCell(col).SetCellValue(dt.Columns[col].ColumnName);
}
#endregion
}
#endregion
#region 表格数据,填充内容
row = sheet.CreateRow(i + rowIndex + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
string drValue = dt.Rows[i][j].ToString();
switch (dt.Columns[j].DataType.ToString())
{
case "System.String"://字符串类型
//if (drValue.Length >= 255)
//{
// drValue = drValue.Substring(0, 252) + "...";
//}
cell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
if (DateTime.TryParse(drValue, out dateV))
{
cell.SetCellValue(dateV);
}
break;
case "System.Boolean"://布尔型
bool boolV = false;
if (bool.TryParse(drValue, out boolV))
{
cell.SetCellValue(boolV);
}
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
if (int.TryParse(drValue, out intV))
{
cell.SetCellValue(intV);
}
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
if (double.TryParse(drValue, out doubV))
{
cell.SetCellValue(doubV);
}
break;
case "System.DBNull"://空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
}
#endregion
#region 自动列宽
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// sheet.AutoSizeColumn(i);//列宽自适应，只对英文和数字有效，但是其实还是比实际文本要宽
// int columnWidth = sheet.GetColumnWidth(i) / 256;//获取当前列宽度
// for (int j = 1; j <= sheet.LastRowNum; j++)
// {
// row = sheet.GetRow(j);
// ICell cell = row.GetCell(i);
// int contentLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;获取当前单元格的内容宽度
// columnWidth = columnWidth < contentLength ? contentLength : columnWidth;
// }
// if (columnWidth > 255)
// {
// sheet.SetColumnWidth(i, 255 * 256);
// }
// else
// {
// sheet.SetColumnWidth(i, columnWidth * 256);//经过测试200比较合适。
// }
//}
#endregion
}
}
/// <summary>
/// 获得Workbook对象
/// </summary>
/// <param name="fs">文件流</param>
/// <returns></returns>
private static IWorkbook GetWorkbook(FileStream fs)
{
IWorkbook workbook = null;
string fileExt = Path.GetExtension(fs.Name).ToLower();
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
#region 右击文件 属性信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "杭州今奥";
((HSSFWorkbook)workbook).DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "hzja"; //填加xls文件作者信息
si.ApplicationName = "杭州今奥网站平台"; //填加xls文件创建程序信息
//si.LastAuthor = ""; //填加xls文件最后保存者信息
//si.Comments = ""; //填加xls文件作者信息
//si.Title = ""; //填加xls文件标题信息
//si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
((HSSFWorkbook)workbook).SummaryInformation = si;
#endregion
}
return workbook;
}
#endregion
}
public static class NPOIExtension
{
public static object Value(this ICell cell)
{
if (cell == null)
{
return null;
}
return GetCellValue(cell, cell.CellType);
}
private static object GetCellValue(ICell cell, CellType type)
{
switch (type)
{
case CellType.Boolean:
return cell.BooleanCellValue;
case CellType.Error:
return cell.ErrorCellValue;
case CellType.Formula:
if (cell.CachedFormulaResultType == CellType.Formula)
{
return null;
}
return GetCellValue(cell, cell.CachedFormulaResultType);
case CellType.Numeric:
return cell.NumericCellValue;
case CellType.String:
return cell.StringCellValue;
case CellType.Blank:
case CellType.Unknown:
return cell;
}
return null;
}
}
}
06-10
361
361
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
