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-上傳-前期準備