csharp: Export or Import excel using NPOI



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
        }
    }
}