NPOI初级教程
糜运良
NPOI
https://archive.codeplex.com/?p=npoi
NPOI下载官网http://npoi.codeplex.com
下载解压，里面有个dotnet4的文件夹，把它拖到自己的项目中，把里面的.dll全部添加引用
基础概念
excel2007：xlsx
XSSFWorkbook wb;
XSSFSheet sh;
excel2003 ：xls
HSSFWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet("Images");
IRow dataRow = confidence_sheet.CreateRow(i);
ICell cell = dataRow.CreateCell(j);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellstyle;
读取现有的Excel
HSSFWorkbook wb;
FileStream file;
file = new FileStream(filepath, FileMode.Open, FileAccess.Read);
wb = new HSSFWorkbook(file);
file.Close();
// 获取已有表
ISheet image_sheet = wk.GetSheet("Images");
ISheet confidence_sheet = wk.GetSheet("Confidence");
int rowsCount = image_sheet .PhysicalNumberOfRows; //取行Excel的最大行数
int colsCount = image_sheet .GetRow(0).PhysicalNumberOfCells;//取得Excel的列数
写excel
var workBook = new HSSFWorkbook();
ISheet sheet = null;
sheet = workBook.CreateSheet("sheet1");//创建表格
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("姓名");
workBook.SetActiveSheet(0);//设置默认表格
string filePath=@"test.xls";
using (FileStream fs = new FileStream(filePath, FileMode.Create))
{
workBook.Write(fs);
}
https://blog.csdn.net/mouday/article/details/81049219
C# 使用NPOI操作Excel文件
c#开源Excel操作库–NPOI
使用Npoi向Excel中插入图片
https://www.cnblogs.com/wei325/p/4748324.html
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
using (FileStream fs = File.OpenRead(@"F:\\PycharmWorkPlace\\test2excel\\test.xlsx")) //打开myxls.xls文件{
{
XSSFWorkbook wb = new XSSFWorkbook(fs);
// HSSFWorkbook wk = new HSSFWorkbook(fs);
ISheet sh = wb.GetSheet("Sheet1");
Console.WriteLine(sh.GetRow(0).GetCell(0).StringCellValue);
Console.ReadLine();
}
}
}
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet( "Sheet1") as HSSFSheet;
IRow row = sheet.CreateRow(0);
row.Height = 30 * 20;
ICell cellTitle = row.CreateCell(0);
titleHeader.Alignment = HorizontalAlignment.Center;
titleHeader.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 14;
font.FontName = "微软雅黑";
font.IsBold = true;
cellTitle.SetFont(font);
cellTitle.SetCellValue(titleName)
Color c = Color.FromArgb(215, 228, 188);
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex((short)63, c.R, c.G, c.B);
HSSFColor cellColor = palette.FindColor(c.R, c.G, c.B);
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = cellColor.Indexed;
region = new CellRangeAddress(3, 3, 15, columnsCount - 1);
sheet.AddMergedRegion(region);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, HSSFColor.Black.Index);
//列宽自适应，只对英文和数字有效
for (int i = 0; i <= columnsCount; i++)
{
sheet.AutoSizeColumn(i);
}
//列宽自适应中文有效
for (int i = 0; i < 15; i++)
{
int columnWidth = sheet.GetColumnWidth(i) / 256;
for (int rowNum = 4; rowNum < 6 + rowsCount; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(i) != null)
{
ICell currentCell = currentRow.GetCell(i);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(i, columnWidth * 350);
}
//列宽自适应中文有效
for (int i = 15; i < columnsCount; i++)
{
int rowNum;
if (dtSource.Columns[i].ColumnName.Contains("/"))
{
rowNum = 4;
}
else
{
rowNum = 5;
}
int columnWidth = sheet.GetColumnWidth(i) / 256;
for (; rowNum < 6 + rowsCount; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(i) != null)
{
ICell currentCell = currentRow.GetCell(i);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(i, columnWidth * 350);
}
//若没有数据则建立空文档
if (workbook.NumberOfSheets == 0)
{
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
}
//写文件
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin); //ms.Position = 0;
return ms;
if (j == 14 )
{
double db = 0;
if (double.TryParse(objVal.ToString(), out db))
{
cell.SetCellValue(db);
}
}
else
{
SetCellValue(cell, objVal);
}
public static void SetCellValue(ICell eCell, object data)
{
string typeStr = data.GetType().ToString();
switch (typeStr)
{
case "System.String":
eCell.SetCellValue(data.ToString());
break;
case "System.DateTime":
System.DateTime dateV;
System.DateTime.TryParse(data.ToString(), out dateV);
eCell.SetCellValue(dateV.ToString("yyyy/MM/dd"));
break;
case "System.Boolean":
bool boolV = false;
bool.TryParse(data.ToString(), out boolV);
eCell.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(data.ToString(), out intV);
eCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(data.ToString(), out doubV);
eCell.SetCellValue(doubV);
break;
case "System.DBNull":
eCell.SetCellValue("");
break;
default:
eCell.SetCellValue("");
break;
}
}
类似资料：