简单介绍下NPOI对EXCEL的操作，使用NPOI不必要安装有office软件，但是使用微软的EXCEL组建操作 则必须要求有安装office软件。
1.创建EXCEL:
HSSFWorkbook workBook = new HSSFWorkbook();//创建的工作薄文件只是在内存中
workBook.CreateSheet("sheet1");
workBook.CreateSheet("sheet2");
workBook.CreateSheet("sheet3");
FileStream fs = new FileStream("D:\\testCpg.xls", FileMode.Create);//创建一个文件
workBook.Write(fs);//添加工作薄
fs.Close();
2.为xls文件设置属性
HSSFWorkbook workBook = new HSSFWorkbook();//创建的工作薄文件只是在内存中
workBook.CreateSheet("sheet1");
workBook.CreateSheet("sheet2");
workBook.CreateSheet("sheet3");
//设置属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();//在没有完整读取文件数据的情况下获得文件的摘要信息，同时也可用作桌面搜素的依据
dsi.Company = "公司名";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = “测试”;
//将属性加到工作薄
workBook.DocumentSummaryInformation = dsi;
workBook.SummaryInformation = si;
FileStream fs = new FileStream("D:\\testCpg.xls", FileMode.Create);//创建一个文件
workBook.Write(fs);//添加工作薄
fs.Close();
3.为xls文件添加内容
HSSFWorkbook workBook = new HSSFWorkbook();//创建的工作薄文件只是在内存中
//设置属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();//在没有完整读取文件数据的情况下获得文件的摘要信息，同时也可用作桌面搜素的依据
dsi.Company = "公司名";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = “测试”;
//将属性加到工作薄
workBook.DocumentSummaryInformation = dsi;
workBook.SummaryInformation = si;
Sheet sheet1 = workBook.CreateSheet("sheet1");
Row row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("This is a Sample 1");
row1.CreateCell(1).SetCellValue("This is a Sample too 1");
row1.CreateCell(2).SetCellValue("This is a Sample too too 1");
row1.CreateCell(3).SetCellValue("This is a Sample 21");
Row row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("This is a Sample 2");
row2.CreateCell(1).SetCellValue("This is a Sample too 2");
row2.CreateCell(2).SetCellValue("This is a Sample too too 2");
row2.CreateCell(3).SetCellValue("This is a Sample 22");
Row row3 = sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue("This is a Sample 3");
row3.CreateCell(1).SetCellValue("This is a Sample too 3");
row3.CreateCell(2).SetCellValue("This is a Sample too too 3");
row3.CreateCell(3).SetCellValue("This is a Sample 23");
#region 加批注
// 批注主要有三个属性需要设置，一个是批注的位置和大小、一个是批注的文本、还有一个是批注的作者。
NPOI.SS.UserModel.Drawing patr = sheet1.CreateDrawingPatriarch();
Row row = sheet1.GetRow(1);
NPOI.SS.UserModel.Comment comment = null;
Cell cell = row.GetCell(1);//为第二行第二列加批注
CreationHelper factory = workBook.GetCreationHelper();
ClientAnchor anchor = null;//设置批注的位置和大小
anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex + 2;//批注起始位置的纵坐标（当前单元格位置+2）
anchor.Col2 = cell.ColumnIndex + 3;//批注结束位置的纵坐标
anchor.Row1 = row.RowNum;//批注起始位置的横坐标
anchor.Row2 = row.RowNum + 3;//批注结束位置的横坐标
//anchor.Dx1 = 10;
//anchor.Dy1 = 15;
//anchor.Dx2 = 20;
//anchor.Dy2 = 25;
comment = patr.CreateCellComment(anchor);//将位置添加到批注
//comment.Visible = true;//默认是隐藏的
comment.Author = "chipenggang";//批注作者
comment.String = new HSSFRichTextString("批注测试数据");//批注文本
cell.CellComment = (comment);//将批注赋给单元格
#endregion
/*
注意：这里的行在Excel里是从1开始的，但是NPOI内部是从0开始的；
列在Excel里面是用字母表示的，而NPOI中也是用从0开始的数字表示的，所以要注意转换。
如果你要获得某一个已经创建的单元格对象，可以用下面的代码：
sheet1.GetRow(row_index).GetCell(column_index);
*/
workBook.CreateSheet("sheet2");
workBook.CreateSheet("sheet3");
FileStream fs = new FileStream("D:\\testCpg.xls", FileMode.Create);//创建一个文件
workBook.Write(fs);//添加工作薄
fs.Close();
4.读取EXCEL文件
FileStream fs = new FileStream("D:\\testCpg.xls", FileMode.Open, FileAccess.Read);
using (NPOI.SS.UserModel.Workbook workBook = new HSSFWorkbook(fs))
{
Sheet sheet1 = workBook.GetSheetAt(0);
Sheet sheet2 = workBook.GetSheetAt(1);
Sheet sheet3 = workBook.GetSheetAt(2);
int rowCount = sheet1.LastRowNum;//最后一行的索引（从0开始），即sheet1有多少行
int rowCounts = sheet1.PhysicalNumberOfRows;//sheet1有多少行
var cellCount = sheet1.GetRow(0).LastCellNum;//某一行最后一列的索引
for (int i = 0; i <= rowCount; i++)
{
Row row = sheet1.GetRow(i);//获取第i行
for (int j = 0; j < cellCount; j++)
{
Cell cell = row.GetCell(j);//获取i行j列
Label1.Text += cell.StringCellValue + "--";//获取此列的内容（注意：此处的类型，类型不同会异常）
}
Label1.Text += "<br/>";
}
}
fs.Close();
最近整理了一篇操作类：共大家使用，不足之处，欢迎提出宝贵意见 。
///
/// Excel2007/2003数据导入导出助手类
///
public class ExcelHelper
{
///
/// Excel转换成DataSet
///
/// excel文件路径
///
返回DataSet
public static DataSet ExcelToDataSet(string excelPath)
{
return ExcelToDataSet(excelPath, true);
}
///
/// Excel转换成DataSet
///
/// excel文件路径
/// excel第一行是否表头
///
返回DataSet
public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader)
{
int sheetCount;
return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
}
///
/// Excel转换成DataSet
///
/// excel文件路径
/// excel第一行是否表头
/// sheet的个数
///
返回DataSet
public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader, out int sheetCount)
{
using (DataSet ds = new DataSet())
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fileStream);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);
sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; ++i)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
ds.Tables.Add(dt);
}
return ds;
}
}
}
///
/// Excel数据转换到DataSet
///
/// 数据流
///
public static DataSet ExcelStreamToDataSet(Stream fs)
{
using (DataSet ds = new DataSet())
{
IWorkbook workbook = WorkbookFactory.Create(fs);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);
var sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; ++i)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable dt = ExcelToDataTable(sheet, evaluator, true);
ds.Tables.Add(dt);
}
return ds;
}
}
///
/// excel转换成DataTable
///
/// excel文件的路径
/// sheet的名称
///
返回Datatable
public static DataTable ExcelToDataTable(string excelPath, string sheetName)
{
return ExcelToDataTable(excelPath, sheetName, true);
}
///
/// excel转换成DataTable
///
/// excel文件的路径
/// sheet的名称
/// excel第一行是否表头
///
返回Datatable
public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fileStream);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);
ISheet sheet = workbook.GetSheet(sheetName);
return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
}
}
///
/// 用NPOI直接读取excel返回DataTable
///
/// 文件流
/// Sheet序号
/// 开始行号
/// 总数
/// 总行数
/// 主键
///
public static DataTable ReadExcelToDataTable(Stream excelFileStream, int sheetIndex, int startRowIndex, out int totalCount, int getRowCount = 0, string primaryKey = "")
{
HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
DataTable table = new DataTable();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(startRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
totalCount = sheet.LastRowNum;
int rowCount = getRowCount > 0 ? (sheetIndex + getRowCount > sheet.LastRowNum ? sheet.LastRowNum : sheetIndex + 1 + getRowCount) : sheet.LastRowNum;
for (int i = startRowIndex + 1; i <= rowCount; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
if (table.Columns.Contains(primaryKey) && !string.IsNullOrWhiteSpace(primaryKey))
{
// 排除指定列为空的行
if (!dataRow.IsNull(primaryKey))
{
table.Rows.Add(dataRow);
}
}
}
excelFileStream.Close(); 由外部关闭文件
workbook = null;
sheet = null;
return table;
}
///
/// DataTable导出到Excel2007或者Excel2003文件，根据保存格式自动识别
///
/// 源DataTable
/// 保存位置
public static void Export(DataTable dtSource, string strFileName)
{
string fileType = strFileName.Substring(strFileName.LastIndexOf('.'));
using (MemoryStream ms = Export(dtSource, fileType.ToLower().Equals(".xlsx")))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
///
/// IList泛型导出到Excel2007或者Excel2003文件，根据保存格式自动识别
///
///
泛型类
/// 源dataList
/// 列头
/// 表头
public static void Export
(IList
dataList, string strFileName, string[] propertyName = null)
{
DataTable dataTable = ToDataTable
(dataList, propertyName);
Export(dataTable, strFileName);
}
///
/// DataTable导出Excel2007或者Excel2003的MemoryStream，根据保存格式自动识别
///
///
源DataTable
///
是否excel2007
public static MemoryStream Export(DataTable dtSource, bool is2007 = false)
{
IWorkbook workbook;
if (is2007)
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
ISheet sheet = workbook.CreateSheet();
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] = 25;
}
int rowIndex = 0;
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
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] + 1) * 256);
}
rowIndex++;
foreach (DataRow row in dtSource.Rows)
{
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 = 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(string.Empty);
break;
default:
newCell.SetCellValue(string.Empty);
break;
}
}
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
return ms;
}
}
///
/// Excel导出到DataTable
///
///
工作表
///
IFormulaEvaluator
///
第一行是否作为表头
///
private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
{
if (firstRowAsHeader)
{
return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
}
else
{
return ExcelToDataTable(sheet, evaluator);
}
}
///
/// 生成表头
///
///
工作表
///
IFormulaEvaluator
///
private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
IRow firstRow = sheet.GetRow(0);
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dr = dt.NewRow();
FillDataRowBySheetRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName;
return dt;
}
}
///
/// 导出数据到DataTable
///
///
工作表
///
格式化
///
private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != 0)
{
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
dt.Columns.Add(string.Format("F{0}", i), typeof(string));
}
for (int i = 0; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dr = dt.NewRow();
FillDataRowBySheetRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
}
dt.TableName = sheet.SheetName;
return dt;
}
}
///
/// 将IRow数据填充到DataRow
///
///
IRow
///
IFormulaEvaluator
///
DataRow
private static void FillDataRowBySheetRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for (int j = 0; j < dr.Table.Columns.Count; j++)
{
ICell cell = row.GetCell(j);
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);
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
}
}
}
}
}
///
/// 获取列数
///
///
工作表
///
private static int GetCellCount(ISheet sheet)
{
int firstRowNum = sheet.FirstRowNum;
int cellCount = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row != null && row.LastCellNum > cellCount)
{
cellCount = row.LastCellNum;
}
}
return cellCount;
}
///
/// 转换数据到DataTable
///
///
数据类型
///
数据集合
///
相应属性
///
private static DataTable ToDataTable
(IList
data, string[] propertyName = null) { List
propertyNameList = new List
(); if (propertyName != null) { propertyNameList.AddRange(propertyName); } PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); for (int i = 0; i < props.Count; i++) { PropertyDescriptor prop = props[i]; if (propertyNameList.Count == 0) { table.Columns.Add(prop.Name, prop.PropertyType); } else if (propertyNameList.Contains(prop.Name)) { table.Columns.Add(prop.Name, prop.PropertyType); } } object[] values = new object[table.Columns.Count]; foreach (T item in data) { for (int i = 0; i < table.Columns.Count; i++) { values[i] = props[table.Columns[i].ColumnName].GetValue(item); } table.Rows.Add(values); } return table; } }
先简单介绍到这，更多内容操作以及组件和说明文档下载地址如下：
组件及文档下载地址：http://download.csdn.net/detail/dominates/6383707
786
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
