直接上代码
/// <summary>
/// Office操作帮助类
/// </summary>
public class NPOIClass
{
/// <summary>
/// Excel转换成DataTable(.xls)
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="sheetName">sheet名称</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string filePath, string sheetName)
{
var dt = new DataTable();
using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new XSSFWorkbook(file);
ISheet sheet = null;
if (string.IsNullOrEmpty(sheetName))
{
sheet = xssfworkbook.GetSheetAt(0);
}
else
{
sheet = xssfworkbook.GetSheet(sheetName);
}
var rows = sheet.GetRowEnumerator();
if (rows.MoveNext())
{
var row = (XSSFRow)rows.Current;
foreach (ICell cell in row.Cells)
{
switch (cell.CellType)
{
case CellType.Blank:
dt.Columns.Add("[null]");
break;
case CellType.Boolean:
dt.Columns.Add(cell.BooleanCellValue.ToString());
break;
case CellType.Numeric:
dt.Columns.Add(cell.ToString());
break;
case CellType.String:
dt.Columns.Add(cell.StringCellValue);
break;
case CellType.Error:
//dt.Columns.Add(cell.ErrorCellValue);
break;
case CellType.Formula:
try
{
dt.Columns.Add(cell.NumericCellValue.ToString());
}
catch
{
dt.Columns.Add(cell.StringCellValue);
}
break;
default:
dt.Columns.Add(cell.CellFormula);
break;
}
}
//for (var j = 0; j < row.ce; j++)
//{
// dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//}
}
while (rows.MoveNext())
{
var row = (XSSFRow)rows.Current;
var dr = dt.NewRow();
for (var i = 0; i < row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
switch (cell.CellType)
{
case CellType.Blank:
dr[i] = "[null]";
break;
case CellType.Boolean:
dr[i] = cell.BooleanCellValue;
break;
case CellType.Numeric:
dr[i] = cell.ToString();
break;
case CellType.String:
dr[i] = cell.StringCellValue;
break;
case CellType.Error:
dr[i] = cell.ErrorCellValue;
break;
case CellType.Formula:
try
{
dr[i] = cell.NumericCellValue;
}
catch
{
dr[i] = cell.StringCellValue;
}
break;
default:
dr[i] = "=" + cell.CellFormula;
break;
}
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// Excel转换成DataSet(.xlsx/.xls)
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string filePath, out string strMsg)
{
strMsg = "";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string fileType = Path.GetExtension(filePath).ToLower();
string fileName = Path.GetFileName(filePath).ToLower();
try
{
ISheet sheet = null;
int sheetNumber = 0;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (fileType == ".xlsx")
{
// 2007版本
XSSFWorkbook workbook = new XSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
Console.WriteLine("Sheet数据获取失败,原因:" + strMsg);
}
}
}
}
else if (fileType == ".xls")
{
// 2003版本
HSSFWorkbook workbook = new HSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
Console.WriteLine("Sheet数据获取失败,原因:" + strMsg);
}
}
}
}
return ds;
}
catch (Exception ex)
{
strMsg = ex.Message;
return null;
}
}
/// <summary>
/// 获取sheet表对应的DataTable
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="strMsg"></param>
/// <returns></returns>
private static DataTable GetSheetDataTable(ISheet sheet, out string strMsg)
{
strMsg = "";
DataTable dt = new DataTable();
string sheetName = sheet.SheetName;
int startIndex = 0;// sheet.FirstRowNum;
int lastIndex = sheet.LastRowNum;
//最大列数
int cellCount = 0;
IRow maxRow = sheet.GetRow(0);
for (int i = startIndex; i <= lastIndex; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && cellCount < row.LastCellNum)
{
cellCount = row.LastCellNum;
maxRow = row;
}
}
//列名设置
try
{
for (int i = 0; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum
{
dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
//DataColumn column = new DataColumn("Column" + (i + 1).ToString());
//dt.Columns.Add(column);
}
}
catch
{
strMsg = "工作表" + sheetName + "中无数据";
return null;
}
//数据填充
for (int i = startIndex; i <= lastIndex; i++)
{
IRow row = sheet.GetRow(i);
DataRow drNew = dt.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
{
if (row.GetCell(j) != null)
{
ICell cell = row.GetCell(j);
switch (cell.CellType)
{
case CellType.Blank:
drNew[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
drNew[j] = cell.DateCellValue;
else
drNew[j] = cell.NumericCellValue;
if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
drNew[j] = cell.NumericCellValue.ToString("#0.00");
break;
case CellType.String:
drNew[j] = cell.StringCellValue;
break;
case CellType.Formula:
try
{
drNew[j] = cell.NumericCellValue;
if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
drNew[j] = cell.NumericCellValue.ToString("#0.00");
}
catch
{
try
{
drNew[j] = cell.StringCellValue;
}
catch { }
}
break;
default:
drNew[j] = cell.StringCellValue;
break;
}
}
}
}
dt.Rows.Add(drNew);
}
return dt;
}
/// <summary>
/// 导出到excel
/// </summary>
/// <param name="TableName"></param>
/// <param name="FileName"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public MemoryStream ExportDataToExcel(DataTable TableName, string FileName, string sheetName)
{
//获得文件路径
string localFilePath = FileName.ToString();
//数据初始化
int TotalCount; //总行数
int RowRead = 0; //已读行数
int Percent = 0; //百分比
TotalCount = TableName.Rows.Count;
//NPOI
IWorkbook workbook;
string FileExt = Path.GetExtension(localFilePath).ToLower();
if (FileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (FileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
if (workbook == null)
{
return null;
}
ISheet sheet = string.IsNullOrEmpty(sheetName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName);
//秒钟
try
{
//读取标题
IRow rowHeader = sheet.CreateRow(0);
for (int i = 0; i < TableName.Columns.Count; i++)
{
ICell cell = rowHeader.CreateCell(i);
cell.SetCellValue(TableName.Columns[i].ColumnName);
}
//读取数据
for (int i = 0; i < TableName.Rows.Count; i++)
{
IRow rowData = sheet.CreateRow(i + 1);
for (int j = 0; j < TableName.Columns.Count; j++)
{
ICell cell = rowData.CreateCell(j);
cell.SetCellValue(TableName.Rows[i][j].ToString());
}
//状态栏显示
RowRead++;
Percent = (int)(100 * RowRead / TotalCount);
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
byte[] buf = stream.ToArray();
stream.Flush();
stream.Close();
stream.Dispose();
if (File.Exists(localFilePath))
{
File.Delete(localFilePath);
}
//保存为Excel文件
using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
fs.Close();
}
return stream;
}
catch (Exception ex)
{
throw ex;
}
}//方法结束
/// <summary>
/// 导出到excel
/// </summary>
/// <param name="TableName"></param>
/// <param name="fileExt">文件后缀名</param>
/// <param name="sheetName"></param>
/// <returns></returns>
public MemoryStream ExportDataToExcelInMemory(DataTable TableName, string sheetName, string fileExt)
{
//数据初始化
int TotalCount; //总行数
int RowRead = 0; //已读行数
int Percent = 0; //百分比
TotalCount = TableName.Rows.Count;
//NPOI
IWorkbook workbook;
if (fileExt == "xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == "xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
if (workbook == null)
{
return null;
}
ISheet sheet = string.IsNullOrEmpty(sheetName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName);
//秒钟
try
{
//读取标题
IRow rowHeader = sheet.CreateRow(0);
for (int i = 0; i < TableName.Columns.Count; i++)
{
ICell cell = rowHeader.CreateCell(i);
cell.SetCellValue(TableName.Columns[i].ColumnName);
}
//读取数据
for (int i = 0; i < TableName.Rows.Count; i++)
{
IRow rowData = sheet.CreateRow(i + 1);
for (int j = 0; j < TableName.Columns.Count; j++)
{
ICell cell = rowData.CreateCell(j);
cell.SetCellValue(TableName.Rows[i][j].ToString());
}
//状态栏显示
RowRead++;
Percent = (int)(100 * RowRead / TotalCount);
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
return stream;
}
catch (Exception ex)
{
throw ex;
}
}//方法结束
}