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 2003using NPOI.POIFS.FileSystem;using NPOI.SS.UserModel;namespace NPOIExcelDemo{ /// /// /// public partial class Form3 : Form { string extractFile = Environment.CurrentDirectory + @"\Sample.xls"; string result = Environment.CurrentDirectory + @"\result1.xls"; /// /// /// public Form3() { InitializeComponent(); } /// /// 涂聚文 /// 20150730 /// EXCEL 2003 /// /// /// 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 /// /// /// /// /// 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; } /// /// /// /// /// 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 2007namespace NPOIExcelDemo{ /// /// /// public partial class Form1 : Form { string extractFile = Environment.CurrentDirectory + @"\Sample.xls"; string result = Environment.CurrentDirectory + @"\result.xls"; /// /// /// public Form1() { InitializeComponent(); } /// /// 涂聚文 /// 20150730 /// EXCEL 2007 /// /// /// 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()); } } /// /// /// /// /// 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; } /// /// /// /// /// 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 } } } /// /// /// /// /// /// /// /// /// /// ///