C#中npoi操作Excel[版本2.0.1读写2003、2007格式]

public static void test1()

{

NpoiHelper np = new NpoiHelper();

DataTable dt1 = np.ReadExcel(AppDomain.CurrentDomain.BaseDirectory + "1测试数据.xls", 2).Tables[0];//读2003格式数据

DataSet ds1 = new DataSet();

ds1.Tables.Add(dt1.Copy());

ds1.AcceptChanges();

string SaveFileName = "output1.xls";

np.CreateExcel2003(SaveFileName, "sheet001", ds1, 0);//写2003格式数据

}

public static void test2()

DataTable dt1 = np.ReadExcel(AppDomain.CurrentDomain.BaseDirectory + "2测试数据.xlsx", 2).Tables[0];//读2007格式数据

string SaveFileName = "output2.xlsx";

np.CreateExcel2007(SaveFileName, "sheet001", ds1, 0);//写2007格式数据

Console.ReadKey();

using System;

using System.Collections.Generic;

using System.Text;

using System.IO;

using NPOI.HSSF.UserModel;

using System.Data;

using System.Collections;

using System.Drawing;

using NPOI.HSSF.Util;

using NPOI.SS.UserModel;

using NPOI.HPSF;

using NPOI.XSSF.UserModel;

namespace Tools.Common

{

/// <summary>

/// Excel文件到DataSet的转换类

/// </summary>

public class NpoiHelper

{

#region 读取Excel文件内容转换为DataSet

/// <summary>

/// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]

/// </summary>

/// <param name="FileName">文件绝对路径</param>

/// <param name="startRow">数据开始行数(1为第一行)</param>

/// <param name="ColumnDataType">每列的数据类型</param>

/// <returns></returns>

public DataSet ReadExcel(string FileName, int startRow, params NpoiDataType[] ColumnDataType)

int ertime = 0;

int intime = 0;

DataSet ds = new DataSet("ds");

DataTable dt = new DataTable("dt");

DataRow dr;

StringBuilder sb = new StringBuilder();

using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))

{

IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口，自动识别excel2003/2007格式

ISheet sheet = workbook.GetSheetAt(0);//得到里面第一个sheet

int j;

IRow row;

#region ColumnDataType赋值

if (ColumnDataType.Length <= 0)

{

row = sheet.GetRow(startRow - 1);//得到第i行

ColumnDataType = new NpoiDataType[row.LastCellNum];

for (int i = 0; i < row.LastCellNum; i++)

{

ICell hs = row.GetCell(i);

ColumnDataType[i] = GetCellDataType(hs);

}

#endregion

for (j = 0; j < ColumnDataType.Length; j++)

Type tp = GetDataTableType(ColumnDataType[j]);

dt.Columns.Add("c" + j, tp);

for (int i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)

row = sheet.GetRow(i);//得到第i行

if (row == null) continue;

try

dr = dt.NewRow();

for (j = 0; j < ColumnDataType.Length; j++)

{

dr["c" + j] = GetCellData(ColumnDataType[j], row, j);

}

dt.Rows.Add(dr);

intime++;

catch (Exception er)

ertime++;

sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message));

continue;

ds.Tables.Add(dt);

}

if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());

return ds;

#endregion

Color LevelOneColor = Color.Green;

Color LevelTwoColor = Color.FromArgb(201, 217, 243);

Color LevelThreeColor = Color.FromArgb(231, 238, 248);

Color LevelFourColor = Color.FromArgb(232, 230, 231);

Color LevelFiveColor = Color.FromArgb(250, 252, 213);

#region 从DataSet导出到MemoryStream流2003

/// 从DataSet导出到MemoryStream流2003

/// <param name="SaveFileName">文件保存路径</param>

/// <param name="SheetName">Excel文件中的Sheet名称</param>

/// <param name="ds">存储数据的DataSet</param>

/// <param name="startRow">从哪一行开始写入，从0开始</param>

/// <param name="datatypes">DataSet中的各列对应的数据类型</param>

public bool CreateExcel2003(string SaveFileName, string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)

try

if (startRow < 0) startRow = 0;

HSSFWorkbook wb = new HSSFWorkbook();

wb = new HSSFWorkbook();

DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

dsi.Company = "pkm";

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

si.Title =

si.Subject = "automatic genereted document";

si.Author = "pkm";

wb.DocumentSummaryInformation = dsi;

wb.SummaryInformation = si;

ISheet sheet = wb.CreateSheet(SheetName);

//sheet.SetColumnWidth(0, 50 * 256);

//sheet.SetColumnWidth(1, 100 * 256);

ICell cell;

DataRow dr;

int maxLength = 0;

int curLength = 0;

object columnValue;

DataTable dt = ds.Tables[0];

if (datatypes.Length < dt.Columns.Count)

datatypes = new NpoiDataType[dt.Columns.Count];

for (int i = 0; i < dt.Columns.Count; i++)

string dtcolumntype = dt.Columns[i].DataType.Name.ToLower();

switch (dtcolumntype)

case "string": datatypes[i] = NpoiDataType.String;

break;

case "datetime": datatypes[i] = NpoiDataType.Datetime;

case "boolean": datatypes[i] = NpoiDataType.Bool;

case "double": datatypes[i] = NpoiDataType.Numeric;

default: datatypes[i] = NpoiDataType.String;

#region 创建表头

row = sheet.CreateRow(0);//创建第i行

ICellStyle style1 = wb.CreateCellStyle();//样式

IFont font1 = wb.CreateFont();//字体

font1.Color = HSSFColor.WHITE.index;//字体颜色

font1.Boldweight = (short)FontBoldWeight.BOLD;//字体加粗样式

//style1.FillBackgroundColor = HSSFColor.WHITE.index;//GetXLColour(wb, LevelOneColor);// 设置图案色

style1.FillForegroundColor = HSSFColor.GREEN.index;//GetXLColour(wb, LevelOneColor);// 设置背景色

style1.FillPattern = FillPatternType.SOLID_FOREGROUND;

style1.SetFont(font1);//样式里的字体设置具体的字体样式

style1.Alignment = HorizontalAlignment.CENTER;//文字水平对齐方式

style1.VerticalAlignment = VerticalAlignment.CENTER;//文字垂直对齐方式

row.HeightInPoints = 25;

for (j = 0; j < dt.Columns.Count; j++)

columnValue = dt.Columns[j].ColumnName;

curLength = Encoding.Default.GetByteCount(columnValue.ToString());

maxLength = (maxLength < curLength ? curLength : maxLength);

int colounwidth = 256 * maxLength;

sheet.SetColumnWidth(j, colounwidth);

cell = row.CreateCell(j);//创建第0行的第j列

cell.CellStyle = style1;//单元格式设置样式

try

cell.SetCellType(CellType.STRING);

cell.SetCellValue(columnValue.ToString());

catch { }

catch

#region 创建每一行

for (int i = startRow; i < ds.Tables[0].Rows.Count; i++)

dr = ds.Tables[0].Rows[i];

row = sheet.CreateRow(i + 1);//创建第i行

for (j = 0; j < dt.Columns.Count; j++)

columnValue = dr[j];

curLength = Encoding.Default.GetByteCount(columnValue.ToString());

maxLength = (maxLength < curLength ? curLength : maxLength);

int colounwidth = 256 * maxLength;

sheet.SetColumnWidth(j, colounwidth);

cell = row.CreateCell(j);//创建第i行的第j列

#region 插入第j列的数据

try

{

NpoiDataType dtype = datatypes[j];

switch (dtype)

{

case NpoiDataType.String:

{

cell.SetCellType(CellType.STRING);

cell.SetCellValue(columnValue.ToString());

} break;

case NpoiDataType.Datetime:

case NpoiDataType.Numeric:

cell.SetCellType(CellType.NUMERIC);

cell.SetCellValue(Convert.ToDouble(columnValue));

case NpoiDataType.Bool:

cell.SetCellType(CellType.BOOLEAN);

cell.SetCellValue(Convert.ToBoolean(columnValue));

case NpoiDataType.Richtext:

cell.SetCellType(CellType.FORMULA);

}

catch

cell.SetCellType(CellType.STRING);

cell.SetCellValue(columnValue.ToString());

#endregion

catch

continue;

//using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate))//生成文件在服务器上

//{

// wb.Write(fs);

//}

//string SaveFileName = "output.xls";

using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上

wb.Write(fs);

Console.WriteLine("文件保存成功！" + SaveFileName);

return true;

catch (Exception er)

Console.WriteLine("文件保存成功！" + SaveFileName);

return false;

#region 从DataSet导出到MemoryStream流2007

/// 从DataSet导出到MemoryStream流2007

public bool CreateExcel2007(string SaveFileName,string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)

XSSFWorkbook wb = new XSSFWorkbook();

//cell.SetCellType(CellType.STRING);

try

//cell.SetCellType(CellType.STRING);

// cell.SetCellType(CellType.STRING);

//cell.SetCellType(CellType.NUMERIC);

//cell.SetCellType(CellType.BOOLEAN);

// cell.SetCellType(CellType.FORMULA);

//cell.SetCellType(HSSFCell.CELL_TYPE_STRING);

//string SaveFileName = "output.xlsx";

using (FileStream fs = new FileStream(SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上

Console.WriteLine("文件保存失败！" + SaveFileName);

private short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour)

short s = 0;

HSSFPalette XlPalette = workbook.GetCustomPalette();

HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);

if (XlColour == null)

if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)

if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)

//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE= 64;

//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;

XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);

else

XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);

s = XlColour.GetIndex();

else

s = XlColour.GetIndex();

return s;

#region 读Excel-根据NpoiDataType创建的DataTable列的数据类型

/// 读Excel-根据NpoiDataType创建的DataTable列的数据类型

/// <param name="datatype"></param>

private Type GetDataTableType(NpoiDataType datatype)

Type tp = typeof(string);//Type.GetType("System.String")

switch (datatype)

case NpoiDataType.Bool:

tp = typeof(bool);

break;

case NpoiDataType.Datetime:

tp = typeof(DateTime);

case NpoiDataType.Numeric:

tp = typeof(double);

case NpoiDataType.Error:

tp = typeof(string);

case NpoiDataType.Blank:

return tp;

#region 读Excel-得到不同数据类型单元格的数据

/// 读Excel-得到不同数据类型单元格的数据

/// <param name="datatype">数据类型</param>

/// <param name="row">数据中的一行</param>

/// <param name="column">哪列</param>

private object GetCellData(NpoiDataType datatype, IRow row, int column)

case NpoiDataType.String:

return row.GetCell(column).DateCellValue;

return row.GetCell(column).StringCellValue;

return row.GetCell(column).NumericCellValue;

try { return row.GetCell(column).BooleanCellValue; }

catch { return row.GetCell(column).StringCellValue; }

try { return row.GetCell(column).DateCellValue; }

try { return row.GetCell(column).NumericCellValue; }

case NpoiDataType.Richtext:

try { return row.GetCell(column).RichStringCellValue; }

try { return row.GetCell(column).ErrorCellValue; }

try { return row.GetCell(column).StringCellValue; }

catch { return ""; }

default: return "";

#region 获取单元格数据类型

/// 获取单元格数据类型

/// <param name="hs"></param>

private NpoiDataType GetCellDataType(ICell hs)

NpoiDataType dtype;

DateTime t1;

string cellvalue = "";

switch (hs.CellType)

case CellType.BLANK:

dtype = NpoiDataType.String;

cellvalue = hs.StringCellValue;

case CellType.BOOLEAN:

dtype = NpoiDataType.Bool;

case CellType.NUMERIC:

dtype = NpoiDataType.Numeric;

cellvalue = hs.NumericCellValue.ToString();

case CellType.STRING:

case CellType.ERROR:

dtype = NpoiDataType.Error;

case CellType.FORMULA:

default:

dtype = NpoiDataType.Datetime;

if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;

return dtype;

#region 测试代码

}

#region 枚举(Excel单元格数据类型)

/// 枚举(Excel单元格数据类型)

public enum NpoiDataType

/// 字符串类型-值为1

String,

/// 布尔类型-值为2

Bool,

/// 时间类型-值为3

Datetime,

/// 数字类型-值为4

Numeric,

/// 复杂文本类型-值为5

Richtext,

/// 空白

Blank,

/// 错误

Error

#endregion

}

C#中npoi操作Excel[版本2.0.1读写2003、2007格式]

继续阅读

Unity3D中背景音乐和相关音效的添加，背景音乐暂停、停止和播放的设置

Unity游戏开发背景知识

vs编译完提示不支持尝试的执行操作

【C# MySQL】第一次使用C#对MySQL进行操作的心得

C#基础——与C#的第一次邂逅声明：IDE：第一个C#果实：Console浅析：总结：

英文月日年日期格式的实现

C# 字节数组 byte[] 快速比较算法

C# 字节数组、字符串转化字节数组转字符串字符串转字节数组字节数组转整数整数转字节数组字符串转字节字符串转数值数值转字符串

C#用Zlib压缩或解压缩字节数组

C# 比较两个字节数组是否相等

对于VS中scanf报错永久解决，创建源文件自动添加#define _CRT_SECURE_NO_WARNINGS 1

《C#高级编程》读书笔记 -索引

CQ V1.0分词bates(基于双数组tire树)—应该是目前最快的中文分词算法

C#多线程——前台线程和后台线程

C#+ArcEngine代码检测ArcEngine Runtime是否安装以及是否授权

七牛云-C#SDK-上传-前期准备