using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HPSF;
using System.Drawing;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using System.Web; namespace Nopi
{
public static class NOPIHelper
{
#region DataTable导出到Excel /// <summary>
/// 用于Web导出
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
public static void ExportByWeb(DataTable dtSource, string strFileName)
{ HttpContext curContext = HttpContext.Current; // 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); curContext.Response.BinaryWrite(Export(dtSource).GetBuffer());
curContext.Response.End(); } /// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "XXXX";
workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "XXXX"; //填加xls文件作者信息
si.ApplicationName = "导出程序"; //填加xls文件创建程序信息
si.LastAuthor = "XXXX"; //填加xls文件最后保存者信息
si.Comments = "XXXX"; //填加xls文件作者信息
si.Title = "XXXX"; //填加xls文件标题信息
si.Subject = "XXXX";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding().GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = ; i < dtSource.Rows.Count; i++)
{
for (int j = ; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding().GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
} int rowIndex = ; foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == || rowIndex == )
{
if (rowIndex != )
{
sheet = workbook.CreateSheet();
} //#region 表头及样式
//{
// IRow headerRow = sheet.CreateRow(0);
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText); // ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font); // headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//}
//#endregion #region 列头及样式
{
IRow headerRow = sheet.CreateRow(); ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = ;
headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + ) * ); }
}
#endregion rowIndex = ;
}
#endregion #region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
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 = ;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = ;
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();
ms.Position = ;
return ms;
} } /// <summary>
/// 获取属性名称相关联的的单元格索引
/// </summary>
/// <param name="propName"></param>
private static int GetCell(string propName, IRow headerRow)
{
var findCell = headerRow.Cells.First(cell => cell.StringCellValue.Equals(GetChineseHerder(propName)));
if (findCell != null)
{
return headerRow.Cells.IndexOf(findCell);
}
else
{
throw new Exception(string.Format("没找发现与属性{0}相对应的中文属性", propName));
}
} public static string GetChineseHerder(string propName)
{
propName = propName.ToUpper();
switch (propName)
{
case "ZCBM":
return "资产编码";
case "ZCMC":
return "资产名称";
case "GGXH":
return "规格型号";
case "CPJH":
return "车牌井号";
case "AZDD":
return "安装地点";
case "FHSL":
return "复合数量";
case "JSZK":
return "技术状况";
case "SYZK":
return "使用状况";
case "ZRR":
return "责任人";
case "PDSJ":
return "盘点时间";
case "PDZK":
return "盘点状况";
case "PYPKYY":
return "盘盈盘亏原因";
default:
return string.Empty;
}
} //private static IRow GetRow(string zcbm, ISheet sheet)
//{
// for (int i = 0; i <= sheet.LastRowNum; i++)
// {
// if (sheet.GetRow(i).Cells[0].StringCellValue.Equals(zcbm))
// {
// return sheet.GetRow(i);
// }
// } // throw new Exception(string.Format("没找到与资产编码{0}匹配的行", zcbm));
//}
#endregion #region 读取excel中内容 返回DataTable
public static DataTable ExcelToDataTable(string excelPath, string sheetName)
{
return ExcelToDataTable(excelPath, sheetName, true);
}
public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fileStream); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet; return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
}
} private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader)
{
if (firstRowAsHeader)
{
return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
}
else
{
return ExcelToDataTable(sheet, evaluator);
}
}
private static DataTable ExcelToDataTableFirstRowAsHeader(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
HSSFRow firstRow = sheet.GetRow() as HSSFRow;
int cellCount = GetCellCount(sheet); for (int i = ; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + ), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + ), typeof(string));
}
} for (int i = ; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
} dt.TableName = sheet.SheetName;
return dt;
}
}
private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != )
{
int cellCount = GetCellCount(sheet); for (int i = ; i < cellCount; i++)
{
dt.Columns.Add(string.Format("F{0}", i), typeof(string));
} for (int i = ; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
} for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
} dt.TableName = sheet.SheetName;
return dt;
}
}
private static void FillDataRowByHSSFRow(HSSFRow row, HSSFFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for (int j = ; j < dr.Table.Columns.Count; j++)
{
HSSFCell cell = row.GetCell(j) as HSSFCell; if (cell != null)
{
switch (cell.CellType)
{
case CellType.BLANK:
dr[j] = DBNull.Value;
break;
case CellType.BOOLEAN:
dr[j] = cell.BooleanCellValue;
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.STRING:
dr[j] = cell.StringCellValue;
break;
case CellType.ERROR:
dr[j] = cell.ErrorCellValue;
break;
case CellType.FORMULA:
cell = evaluator.EvaluateInCell(cell) as HSSFCell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
}
}
}
}
}
private static int GetCellCount(HSSFSheet sheet)
{
int firstRowNum = sheet.FirstRowNum; int cellCount = ; for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow; if (row != null && row.LastCellNum > cellCount)
{
cellCount = row.LastCellNum;
}
} return cellCount;
}
#endregion #region 读取excel中内容,选择固定的行作为列名 返回DataTable
/**
* 读取excel中内容,选择固定的行作为列名 返回DataTable
* 2015-03-26wytadd
* excelPath url
* sheetName sheet名
* rowNumber 从哪行开始读取数据并作为表头
*/
public static DataTable ExcelToDataTable(string excelPath, string sheetName,int rowNumber)
{
if (rowNumber > )
{
return ExcelToDataTable(excelPath, sheetName, false, rowNumber);
}
else {
return ExcelToDataTable(excelPath, sheetName, true, rowNumber);
} }
public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader, int rowNumber)
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fileStream); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet; return ExcelToDataTable(sheet, evaluator, firstRowAsHeader,rowNumber);
}
} private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader, int rowNumber)
{
if (firstRowAsHeader)
{
return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
}
else
{
if (rowNumber==)
{
return ExcelToDataTable(sheet, evaluator);
}else{
return ExcelToDataTableRowNumberRowAsHeader(sheet, evaluator,rowNumber);
}
}
}
private static DataTable ExcelToDataTableRowNumberRowAsHeader(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, int rowNumber)
{
using (DataTable dt = new DataTable())
{
HSSFRow firstRow = sheet.GetRow(rowNumber-) as HSSFRow;
int cellCount = GetCellCount(sheet); for (int i = ; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
//个别情况需要进行下面的设置，避免有的列为整型的取值会报异常，
firstRow.GetCell(i).SetCellType(CellType.STRING);
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + ), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + ), typeof(string));
}
} for (int i = rowNumber; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
} dt.TableName = sheet.SheetName;
return dt;
}
}
#endregion
}
}
以上是修改的npoi的默认代码调用部分代码场景为
try
{
//遍历导入表返回dt
dt_FromNpoi = NOPIHelper.ExcelToDataTable(Server.MapPath("excelload") + "\\" + in_File.FileName, "租赁情况统计表",3);
}
catch (Exception)
{
Page.RegisterStartupScript("", "<script>alert('导入模板有错误，无法遍历导入模板数据到系统，请重新导入！')</script>");
this.In_Div.Visible = false;
}
其中的3为想要从哪行进行取值，并且将此列作为表头