using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
namespace Helper
{
public class ExcelNPOI
{
private string m_FilePath;
private IWorkbook m_WorkBook;
public ExcelNPOI()
{
}
public ExcelNPOI(string filePath)
{
LoadFile(filePath);
}
public ExcelNPOI(string fileName, Stream stream)
{
LoadFile(fileName, stream);
}
public void LoadFile(string filePath)
{
m_FilePath = filePath;
OpenFile();
}
public void LoadFile(string fileName, Stream stream)
{
m_WorkBook = WorkbookFactory.Create(stream);
}
private void OpenFile()
{
using (FileStream fileSteam = new FileStream(m_FilePath, FileMode.Open))
{
m_WorkBook = WorkbookFactory.Create(fileSteam);
}
}
public int GetNumberOfSheet()
{
return m_WorkBook.NumberOfSheets;
}
#region input data
public string GetSheetName(int sheetIndex)
{
string sheetName = string.Empty;
if (m_WorkBook != null)
{
sheetName = m_WorkBook.GetSheetName(sheetIndex);
}
return sheetName;
}
public int GetLastSheetNumber()
{
int lastSheetNumber = 0;
if (m_WorkBook != null)
{
lastSheetNumber = m_WorkBook.NumberOfSheets;
}
return lastSheetNumber;
}
public int GetLastRowNumber(int sheetIndex)
{
int lastRowNumber = 0;
if (m_WorkBook != null)
{
ISheet sheet = m_WorkBook.GetSheetAt(sheetIndex);
if (sheet != null)
{
lastRowNumber = sheet.LastRowNum;
}
}
return lastRowNumber;
}
public int GetLastCellNumber(int sheetIndex, int rowIndex)
{
int lastCellNumber = 0;
if (m_WorkBook != null)
{
ISheet sheet = m_WorkBook.GetSheetAt(sheetIndex);
if (sheet != null)
{
IRow row = sheet.GetRow(rowIndex);
if (row != null)
{
lastCellNumber = row.LastCellNum;
}
}
}
return lastCellNumber;
}
public object GetData(int sheetIndex, int rowIndex, int cellIndex)
{
object returnValue = null;
if (m_WorkBook != null && cellIndex >= 0)
{
ISheet sheet = m_WorkBook.GetSheetAt(sheetIndex);
returnValue = GetData(sheet, rowIndex, cellIndex);
}
return returnValue;
}
public object GetData(string sheetName, int rowIndex, int cellIndex)
{
object returnValue = null;
if (m_WorkBook != null && cellIndex >= 0)
{
ISheet sheet = m_WorkBook.GetSheet(sheetName);
returnValue = GetData(sheet, rowIndex, cellIndex);
}
return returnValue;
}
private object GetData(ISheet sheet, int rowIndex, int cellIndex)
{
object returnValue = null;
if (sheet != null & sheet.GetRow(0) != null && cellIndex >= 0)
{
IRow sourceRow = sheet.GetRow(rowIndex);
if (sourceRow != null)
{
ICell cell = sourceRow.GetCell(cellIndex);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Boolean:
returnValue = cell.BooleanCellValue;
break;
case CellType.Error:
returnValue = cell.ErrorCellValue;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
returnValue = cell.DateCellValue;
}
else
{
returnValue = cell.NumericCellValue;
}
break;
case CellType.String:
returnValue = cell.StringCellValue;
break;
case CellType.Formula:
{
switch (cell.CachedFormulaResultType)
{
case CellType.Boolean:
returnValue = System.Convert.ToString(cell.BooleanCellValue);
break;
case CellType.Numeric:
returnValue = System.Convert.ToString(cell.NumericCellValue);
break;
case CellType.String:
string formula = cell.StringCellValue;
if (formula != null && formula.Length > 0)
{
returnValue = formula.ToString();
}
else
{
returnValue = null;
}
break;
default:
returnValue = "";
break;
}
};
break;
default:
try
{
returnValue = cell.StringCellValue;
}
catch
{
returnValue = null;
}
break;
}
}
}
}
return returnValue;
}
#endregion output data
#region input data
public bool SetData(int sheetIndex, int rowIndex, int cellIndex, object value)
{
bool blReturn = false;
if (m_WorkBook != null)
{
ISheet sheet = m_WorkBook.GetSheetAt(sheetIndex);
if (sheet == null)
{
sheet = m_WorkBook.CreateSheet();
}
blReturn = SetData(sheet, rowIndex, cellIndex, value);
}
return blReturn;
}
public bool SetData(string sheetName, int rowIndex, int cellIndex, object value)
{
bool blReturn = false;
if (m_WorkBook != null)
{
ISheet sheet = m_WorkBook.GetSheet(sheetName);
if (sheet == null)
{
sheet = m_WorkBook.CreateSheet(sheetName);
}
blReturn = SetData(sheet, rowIndex, cellIndex, value);
}
return blReturn;
}
private bool SetData(ISheet sheet, int rowIndex, int cellIndex, object value)
{
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
ICell cell = row.GetCell(cellIndex);
if (cell == null)
{
cell = row.CreateCell(cellIndex);
}
value = value == null ? string.Empty : value;
switch (value.GetType().Name.ToUpper())
{
case "STRING":
cell.SetCellValue(value.ToString());
cell.SetCellType(CellType.String);
break;
case "DOUBLE":
case "FLOAT":
case "DECIMAL":
case "INT64":
case "INT32":
case "INT":
cell.SetCellValue(double.Parse(value.ToString()));
cell.SetCellType(CellType.Numeric);
break;
case "BOOL":
case "BOOLEN":
cell.SetCellValue(bool.Parse(value.ToString()));
cell.SetCellType(CellType.Boolean);
break;
default:
cell.SetCellValue(value.ToString());
cell.SetCellType(CellType.String);
break;
}
return true;
}
public MemoryStream GetExcelContent()
{
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
if (m_WorkBook != null)
{
m_WorkBook.Write(ms);
}
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return ms;
}
#endregion input data
/ DataSet导出到Excel的MemoryStream
/ <summary>
/ DataSet导出到Excel文件
/ </summary>
/ <param name="dtSource">源DataSet</param>
/ <param name="strFileName">保存位置</param>
//public void DataSetToExcel(DataSet dtSource, string strFileName)
//{
// using (MemoryStream ms = DataSetToExcel(dtSource))
// {
// using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
// {
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// }
// }
//}
/// <summary>
/// DataSet导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataSet</param>
public MemoryStream DataSetToExcel(DataSet ds)
{
XSSFWorkbook workbook = new XSSFWorkbook();
for (int k = 0; k < ds.Tables.Count; k++)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[k].TableName.ToString());
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (DataRow row in ds.Tables[k].Rows)
{
#region 新建表,填充表头,填充列头,样式
if (rowIndex == 0)
{
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in ds.Tables[k].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
// headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in ds.Tables[k].Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
System.DateTime dateV;
System.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
rowIndex++;
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
return ms;
}
}
}
//新建类 重写Npoi流方法
public class NpoiMemoryStream : MemoryStream
{
public NpoiMemoryStream()
{
AllowClose = true;
}
public bool AllowClose { get; set; }
public override void Close()
{
if (AllowClose)
base.Close();
}
}
}
ExcelNPOI
最新推荐文章于 2024-06-09 23:04:45 发布