csharp: Export or Import excel using NPOI

excel 2003:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188

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