excel 2003:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Reflection;
using NPOI;
using NPOI.HSSF.UserModel; //excel 2003
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
namespace NPOIExcelDemo
{
/// <summary>
///
/// </summary>
public partial class Form3 : Form
{
string extractFile = Environment.CurrentDirectory + @"\Sample.xls";
string result = Environment.CurrentDirectory + @"\result1.xls";
/// <summary>
///
/// </summary>
public Form3()
{
InitializeComponent();
}
/// <summary>
/// 涂聚文
/// 20150730
/// EXCEL 2003
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form3_Load(object sender, EventArgs e)
{
try
{
//两个文件的标题和内容要相同,所以数据都变成了字符型的 excel 2003
string file1 = Environment.CurrentDirectory + @"\20150728工资结构.xls";
string file2 = Environment.CurrentDirectory + @"\工资结构.xls";
DataTable dt = new DataTable();
string[] files = new string[] { file1, file2 };
for (int i = 0; i < files.Length; i++)
{
MergeData(files[i], dt);
}
ExportDataTableToExcel(dt, result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
#region
/// <summary>
///
/// </summary>
/// <param name="path"></param>
/// <param name="dt"></param>
private static void MergeData(string extractFile, DataTable dt)
{
if (!File.Exists(extractFile))
{
MessageBox.Show(string.Format("Excel File '{0}' to extract is not found (Current Directory: {1}).", extractFile, Environment.CurrentDirectory));
return;
}
// write data in workbook from xls document.
StreamReader input = new StreamReader(extractFile);
IWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream));
///
foreach (HSSFSheet sheetname in workbook)
{
string s = sheetname.SheetName; //获取工作表名称
}
// read the current table data
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(1);//第二个工作表
// read the current row data
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
// LastCellNum is the number of cells of current rows
int cellCount = headerRow.LastCellNum;
if (dt.Rows.Count == 0)
{
// build header for there is no data after the first implementation
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
// get data as the column header of DataTable
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dt.Columns.Add(column);
}
}
else
{
// TODO: check if the subsequent sheet corresponds
}
// LastRowNum is the number of rows of current table
int rowCount = sheet.LastRowNum + 1;
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
// get data and convert them into character string type, then save them into the rows of datatable
dataRow[j] = row.GetCell(j).ToString(); //要判断不同的数据类型
}
dt.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
}
/// <summary>
///
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
public static void ExportDataTableToExcel(DataTable dtSource, string strFileName)
{
// create workbook XSSF
HSSFWorkbook workbook = new HSSFWorkbook();
// the table named mySheet
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("mySheet");
// create the first row
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
// create the cells in the first row, and add data into these cells circularly
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//create rows on the basis of data from datatable(not including table header), and add data into cells in every row
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString()); //要判断不同的数据类型
}
}
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);// write mySheet table in xls document and save it
}
}
}
#endregion
}
}
excel 2007,2010
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Reflection;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; //excel 2007
namespace NPOIExcelDemo
{
/// <summary>
///
/// </summary>
public partial class Form1 : Form
{
string extractFile = Environment.CurrentDirectory + @"\Sample.xls";
string result = Environment.CurrentDirectory + @"\result.xls";
/// <summary>
///
/// </summary>
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 涂聚文
/// 20150730
/// EXCEL 2007
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
try
{
//两个文件的标题和内容要相同,所以数据都变成了字符型的 excel 2007
string file1 = Environment.CurrentDirectory + @"\20150728工资结构.xlsx";
string file2 = Environment.CurrentDirectory + @"\工资结构.xlsx";
DataTable dt = new DataTable();
string[] files = new string[] { file1, file2 };
for (int i = 0; i < files.Length; i++)
{
MergeData(files[i], dt);
}
ExportDataTableToExcel(dt, result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
/// <summary>
///
/// </summary>
/// <param name="path"></param>
/// <param name="dt"></param>
private static void MergeData(string path, DataTable dt)
{
// write data in workbook from xls document.
XSSFWorkbook workbook = new XSSFWorkbook(path);
///
foreach (XSSFSheet sheetname in workbook)
{
string s= sheetname.SheetName;
}
// read the current table data
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(1);//第二个工作表
// read the current row data
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
// LastCellNum is the number of cells of current rows
int cellCount = headerRow.LastCellNum;
if (dt.Rows.Count == 0)
{
// build header for there is no data after the first implementation
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
// get data as the column header of DataTable
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dt.Columns.Add(column);
}
}
else
{
// TODO: check if the subsequent sheet corresponds
}
// LastRowNum is the number of rows of current table
int rowCount = sheet.LastRowNum + 1;
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
// get data and convert them into character string type, then save them into the rows of datatable
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
}
/// <summary>
///
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
public static void ExportDataTableToExcel(DataTable dtSource, string strFileName)
{
// create workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// the table named mySheet
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("mySheet");
// create the first row
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
// create the cells in the first row, and add data into these cells circularly
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//create rows on the basis of data from datatable(not including table header), and add data into cells in every row
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = (XSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
}
}
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);// write mySheet table in xls document and save it
}
}
}
/// <summary>
///
/// </summary>
/// <param name="cell"></param>
/// <param name="dataFormatter"></param>
/// <param name="formulaEvaluator"></param>
/// <returns></returns>
private static string GetValue(ICell cell, DataFormatter dataFormatter, IFormulaEvaluator formulaEvaluator)
{
string ret = string.Empty;
if (null == cell) { return ret; }
ret = dataFormatter.FormatCellValue(cell, formulaEvaluator);
return ret.Replace("\n", " "); // remove line break
}
/// <summary>
///
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static string GetComment(ICell cell)
{
string ret = string.Empty;
if ((null == cell) || (null == cell.CellComment)) { return ret; }
IRichTextString str = cell.CellComment.String;
if (str != null && str.Length > 0)
{
ret = str.ToString();
}
return ret.Replace("\n", " "); // remove line break
}
}
}
