npoi把xlsx文件转为html,NPOI 将excel转换为datatable或者将datatable转换为excel

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingNPOI.SS.UserModel;usingNPOI.XSSF.UserModel;usingSystem.IO;usingNPOI.HSSF.UserModel;usingNPOI.POIFS.FileSystem;usingNPOI;usingNPOI.OpenXml4Net.OPC;usingSystem.Data;namespaceTransactionToString

{public classOperationExcel

{private intinsertRowIndex;private intinsertRowCount;private DictionaryinsertData;privateIWorkbook workbook;public OperationExcel(int insertRowIndex, int insertRowCount,Dictionary insertData=null)

{if (insertData!=null)

{this.insertData =insertData;

}this.insertRowIndex =insertRowIndex;this.insertRowCount =insertRowCount;

}publicOperationExcel()

{ }private IWorkbook NPOIOpenExcel(stringfilename)

{

IWorkbook myworkBook;

Stream excelStream=OpenResource(filename);if(POIFSFileSystem.HasPOIFSHeader(excelStream))return newHSSFWorkbook(excelStream);if(POIXMLDocument.HasOOXMLHeader(excelStream))

{return newXSSFWorkbook(OPCPackage.Open(excelStream));

}if (filename.EndsWith(".xlsx"))

{return newXSSFWorkbook(excelStream);

}if (filename.EndsWith(".xls"))

{newHSSFWorkbook(excelStream);

}throw new Exception("Your InputStream was neither an OLE2 stream, nor an OOXML stream");

}private Stream OpenResource(stringfilename)

{

FileStream fs= newFileStream(filename, FileMode.Open, FileAccess.Read);returnfs;

}private void InsertRow(ISheet sheet,int insertRowIndex,intinsertRowCount,IRow formatRow)

{

ICellStyle styleText=sheet.Workbook.CreateCellStyle();

IDataFormat dataformat=sheet.Workbook.CreateDataFormat();

styleText.DataFormat= dataformat.GetFormat("@");

sheet.ShiftRows(insertRowIndex, sheet.LastRowNum, insertRowCount,true, false);for (int i = insertRowIndex; i < insertRowIndex+insertRowCount; i++)

{

IRow targetRow= null;

ICell sourceCell= null;

ICell targetCell= null;

targetRow=sheet.CreateRow(i);for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)

{

sourceCell=formatRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);if (sourceCell==null)

{continue;

}

targetCell=targetRow.CreateCell(m);

}

}for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)

{

IRow firstTargetRow=sheet.GetRow(i);

ICell firstSourceCell= null;

ICell firstTargetCell= null;for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)

{

firstSourceCell=formatRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);if (firstSourceCell == null)

{continue;

}

firstTargetCell=firstTargetRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);if (this.insertData!=null&&this.insertData.Count>0)

{

firstTargetCell.SetCellValue(insertData[m]);

}

firstTargetCell.SetCellValue("test");

}

}

}public void WriteToFile(IWorkbook workbook,stringfilename)

{if(File.Exists(filename))

{

File.Delete(filename);

}using (FileStream fs=newFileStream(filename,FileMode.OpenOrCreate,FileAccess.Write))

{

workbook.Write(fs);

fs.Close();

}

}public void OpenExcel(stringfilename)

{

System.Diagnostics.Process process= newSystem.Diagnostics.Process();

process.StartInfo.FileName=filename;

process.StartInfo.ErrorDialog= true;

process.Start();

}public void EditorExcel(string savePath, stringreadPath, OperationExcel oe)

{try{

IWorkbook workbook=oe.NPOIOpenExcel(readPath);if (workbook == null)

{return;

}int sheetNum =workbook.NumberOfSheets;for (int i = 0; i < sheetNum; i++)

{

ISheet mysheet=workbook.GetSheetAt(i);

IRow mySourceRow=mysheet.GetRow(insertRowIndex);

oe.InsertRow(mysheet, insertRowIndex, insertRowCount, mySourceRow);

}

oe.WriteToFile(workbook, savePath);

oe.OpenExcel(savePath);

}catch(Exception ex)

{throw newException(ex.Message);

}

}public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten,stringfileName)

{int i = 0;int j = 0;int count = 0;

ISheet sheet= null;if(File.Exists(fileName))

File.Delete(fileName);var fs = newFileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);if (fileName.IndexOf(".xlsx") > 0)

workbook= newXSSFWorkbook();else if (fileName.IndexOf(".xls") > 0)

workbook= newHSSFWorkbook();try{if (workbook != null)

{

sheet=workbook.CreateSheet(sheetName);

}else{return -1;

}if (isColumnWritten == true)

{

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);returncount;

}catch(Exception ex)

{

Console.WriteLine("Exception:" +ex.Message);return -1;

}

}public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn,stringfileName)

{

ISheet sheet= null;

DataTable data= newDataTable();int startRow = 0;try{var fs = newFileStream(fileName, FileMode.Open, FileAccess.Read);if (fileName.IndexOf(".xlsx") > 0)

workbook= newXSSFWorkbook(fs);else if (fileName.IndexOf(".xls") > 0)

workbook= newHSSFWorkbook(fs);if (sheetName != null)

{

sheet=workbook.GetSheet(sheetName);if (sheet == null)

{

sheet= workbook.GetSheetAt(0);

}

}else{

sheet= workbook.GetSheetAt(0);

}if (sheet != null)

{

IRow firstRow= sheet.GetRow(0);int cellCount =firstRow.LastCellNum;if(isFirstRowColumn)

{for (int i = firstRow.FirstCellNum; i < cellCount; ++i)

{

ICell cell=firstRow.GetCell(i);if (cell != null)

{string cellValue =cell.StringCellValue;if (cellValue != null)

{

DataColumn column= newDataColumn(cellValue);

data.Columns.Add(column);

}

}

}

startRow= sheet.FirstRowNum + 1;

}else{

startRow=sheet.FirstRowNum;

}int rowCount =sheet.LastRowNum;for (int i = startRow; i <= rowCount; ++i)

{

IRow row=sheet.GetRow(i);if (row == null) continue;

DataRow dataRow=data.NewRow();for (int j = row.FirstCellNum; j < cellCount; ++j)

{if (row.GetCell(j) != null)

dataRow[j]=row.GetCell(j).ToString();

}

data.Rows.Add(dataRow);

}

}returndata;

}catch(Exception ex)

{

Console.WriteLine("Exception:" +ex.Message);return null;

}

}

}

}