static IWorkbook ReadFielStreamFormExcel(string fileFullName, EExcelType excel)
{
FileStream fs = new FileStream(fileFullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
IWorkbook excelBook = null;
//当文件是新建的时候没法读取文件流
switch (excel)
{
case EExcelType.Xls:
excelBook = new HSSFWorkbook(fs, true);
break;
case EExcelType.Xlsx:
excelBook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
//其他格式的文件无法加载
/*
* “NPOI.POIFS.FileSystem.NotOLE2FileException”类型的未经处理的异常在 NPOI.dll 中发生
* 其他信息: Invalid header signature; read 0x2C656D614EBFBBEF, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document
*
* “ICSharpCode.SharpZipLib.Zip.ZipException”类型的未经处理的异常在 ICSharpCode.SharpZipLib.dll 中发生
* 其他信息: Wrong Local header signature: 0x4EBFBBEF
*/
break;
}
return(excelBook);
}
public static void DataFillRowsInSheet <T, R>(string fileFullName, EExcelType excel, string targetSheetName, SheetHeadDataToDo fillRowEvent, SheetRowDataToDo <R> fillRowsDataEvent, List <R> rows)
where T : class
where R : class
{
//单元格数据填充处理
#region 定目标sheet页
IWorkbook excelBook = GetExcelWorkBook(fileFullName, excel);
if (excelBook == null)
{ //文件限定不正常
}
//判断工作页是否存在
int index = excelBook.GetSheetIndex(targetSheetName);
ISheet sheet = null;
if (index < 0)
{//不存在则创建
sheet = excelBook.CreateSheet(targetSheetName);
}
else
{
sheet = excelBook.GetSheetAt(index);
}
#endregion
//列头
fillRowEvent(sheet);
//行数据
if (fillRowsDataEvent != null)
{
fillRowsDataEvent(sheet, rows);
}
//数据存储
FileStream fs = new FileStream(fileFullName, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);
fs.Flush();
SaveSheet(excelBook, fs);
}
/// <summary>
/// 只读取指定的工作页
/// </summary>
/// <param name="excelDir"></param>
/// <param name="version"></param>
/// <param name="sheetIndex"></param>
/// <returns></returns>
public static ISheet ReadExcelSheet(string excelDir, EExcelType version, int sheetIndex)
{
IWorkbook booke = GetExcelWorkBook(excelDir, version);
ISheet sheet = booke.GetSheetAt(sheetIndex);
booke.Close();
return(sheet);
}
public static EExcelType SelectExcelVersion(string excelFullPath)
{
FileInfo fi = new FileInfo(excelFullPath);
string ext = fi.Extension;//文件扩展名/文件后缀
EExcelType type = ext.ToLower() == EExcelType.Xls.ToString().ToLower()
? EExcelType.Xls : EExcelType.Xlsx;
return(type);
}
public static void ExcelCuttingPage(string excelDir, int cuttingSheetIndex, int pageSize, ExcelOperateTodoCall doCuttingProcess)
{
if (!File.Exists(excelDir))
{//文件不存在
return;
}
if (pageSize < 1)
{//分割数必须大于0的整数
return;
}
FileInfo fi = new FileInfo(excelDir);
string fullDir = fi.Directory.FullName;
string ext = fi.Extension;//文件扩展名格式
EExcelType et = EExcelType.Xls;
if (ext.Contains(EExcelType.Xls.ToString()))
{
et = EExcelType.Xls;
}
else if (ext.Contains(EExcelType.Xlsx.ToString()))
{
et = EExcelType.Xlsx;
}
string name = fi.Name.Replace(ext, string.Empty);
IWorkbook book = GetExcelWorkBook(excelDir, et);
int si = book.NumberOfSheets;
if (si < cuttingSheetIndex)
{//查询页数目超出限制
book.Close();
return;
}
ISheet sheet = book.GetSheetAt(cuttingSheetIndex);
//读取标题行
int rn = sheet.LastRowNum;
if (rn <= pageSize - 1)
{ //行数目小于切割分割限制数目
book.Close();
return;
}
try
{
DoCuttingExcel(sheet, et, new CuttingParam()
{
CuttingExcelSaveDir = fullDir,
CuttingSheetPageSize = pageSize,
OriginExceleName = name
}, doCuttingProcess);
}
catch (Exception ex)
{
}
book.Close();
}
static void DoCuttingExcel(ISheet sheet, EExcelType type, CuttingParam param, ExcelOperateTodoCall doCuttingProcess)
{
IRow head = sheet.GetRow(0);
int number = sheet.LastRowNum / param.CuttingSheetPageSize;//将要分割多少个excel
Dictionary <int, string> columns = new Dictionary <int, string>();
for (int i = 0; i < head.LastCellNum; i++)
{
ICell cell = head.GetCell(i);
string cn = string.Empty;
if (cell != null)
{
cn = cell.ToString().Trim();
}
columns.Add(i, cn);
}
string dir = param.CuttingExcelSaveDir + "/" + param.OriginExceleName;
CuttingParam call = new CuttingParam()
{
CuttingSheetPageSize = param.CuttingSheetPageSize,
CuttingExcelSaveDir = param.CuttingExcelSaveDir,
CanCuttingPageNumber = number,
Statue = OperateStatue.Will
};
doCuttingProcess(param);
string format = Common.Data.CommonFormat.DateToMinuteIntFormat;
string time = DateTime.Now.ToString(format);//文件夹格式戳
if (Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
//提取列头
for (int i = 0; i < number; i++)
{
call.Statue = OperateStatue.Begin;
call.DoCuttingRowBegin = param.CuttingSheetPageSize * i + 1;
call.DoCuttitRowEnd = param.CuttingSheetPageSize * (i + 1) - 1;
doCuttingProcess(call);
//进行操作
string span = DateTime.Now.ToString(format) + i + "." + (type == EExcelType.Xls?EExcelType.Xls.ToString():EExcelType.Xlsx.ToString());
//只提取列头不为空的单元格
int end = call.DoCuttitRowEnd >= number?number:call.DoCuttitRowEnd;
//创建excel
for (int r = call.DoCuttingRowBegin; r < end; r++)
{
}
//新建excel
call.Statue = OperateStatue.End;
doCuttingProcess(call);
}
}
void WriteDiffRowInBook(string excelFullPath, int writeSheetIndex, string dataSourceFullPath, int sheetIndex, int[] rowIndex)
{
EExcelType version = ExcelHelper.SelectExcelVersion(excelFullPath);
IWorkbook book = ExcelHelper.GetExcelWorkBook(excelFullPath, version);
ISheet sheet = ExcelHelper.ReadExcelSheet(dataSourceFullPath, version, sheetIndex);
IRow head = sheet.GetRow(0);
ISheet excuteSheet = null;
if (book.NumberOfSheets > 0 && book.NumberOfSheets - 1 >= writeSheetIndex)
{
excuteSheet = book.GetSheetAt(writeSheetIndex);
}
else
{
excuteSheet = book.CreateSheet();
}
IRow wr = excuteSheet.CreateRow(0);//这是要写入的头
int cellSize = head.LastCellNum;
for (int i = 0; i < cellSize; i++)
{
ICell cell = head.GetCell(i);
ICell wc = wr.CreateCell(i, cell == null?CellType.String: cell.CellType);
wc.SetCellValue(cell == null?string.Empty: cell.ToString());
}
int rowPoint = 0;
for (int i = 0; i < rowIndex.Length; i++)
{
IRow tr = sheet.GetRow(rowIndex[i]);
rowPoint++;//行位置重新编排
IRow wrRow = excuteSheet.CreateRow(rowPoint);
for (int c = 0; c < cellSize; c++)
{
ICell cell = tr.GetCell(c);
ICell wc = wrRow.CreateCell(c, cell == null ? CellType.String : cell.CellType);
wc.SetCellValue(cell == null ? string.Empty : cell.ToString());
}
}
//保存
FileStream fs = new FileStream(excelFullPath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite); //数据存储
fs.Flush();
book.Write(fs);
fs.Close();
book.Close();
}
/// <summary>
/// 读取excel的工作单元【如果不存在则新建】
/// </summary>
/// <param name="fileFullName"></param>
/// <param name="excel"></param>
/// <returns></returns>
public static IWorkbook GetExcelWorkBook(string fileFullName, EExcelType excel)
{
if (File.Exists(fileFullName))
{
return(ReadFielStreamFormExcel(fileFullName, excel));//文件已存在进行追加时调用
}
//文件不存在直接新建
FileStream fs = new FileStream(fileFullName, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
IWorkbook excelBook = null;
//当文件是新建的时候没法读取文件流
switch (excel)
{
case EExcelType.Xls:
excelBook = new HSSFWorkbook();
break;
case EExcelType.Xlsx:
excelBook = new NPOI.XSSF.UserModel.XSSFWorkbook();
break;
}
return(excelBook);
}
public static void ReadSheet(string excelFullPath, bool readAllSheet, int sheetPageIndex, ReadRowCallBack readRowFun, bool justReadHead)
{
EExcelType type = SelectExcelVersion(excelFullPath);
IWorkbook book = GetExcelWorkBook(excelFullPath, type);
int sheetPage = book.NumberOfSheets;//总共存储多少sheet页
//是否增加页索引判断->
if (!readAllSheet)
{
if (sheetPageIndex > sheetPage)
{//超出行索引没必要进行后续操作
book.Close();
return;
}
ReadSheet(book, sheetPageIndex, readRowFun, justReadHead);
book.Close();
return;
}
for (int i = 0; i < sheetPage; i++)
{
ReadSheet(book, i, readRowFun, justReadHead);
}
book.Close();//是释放流文件
}
public static void DataFillSheet <R>(string fileFullName, EExcelType excel, string targetSheetName, List <ExcelHeadAttribute> head, SheetRowDataToDo <R> fillRowsDataEvent, List <R> rows) where R : class
{
sheetHead = head;
DataFillRowsInSheet <ExcelHeadAttribute, R>(fileFullName, excel, targetSheetName, FillSheetHead, fillRowsDataEvent, rows);
}
public static Stream GetExcelStream(List <DataTable> dts, List <string> sheetNames, EExcelType type)
{
if (dts == null || dts.Count <= 0)
{
return(null);
}
sheetNames = sheetNames ?? new List <string>();
IWorkbook workbook = CreateWorkBook(dts, sheetNames, type);
Stream outputStream = new MemoryStream();
workbook.Write(outputStream);
return(outputStream);
}
