C# Excel操作之读，写，追加

C# 读Excel

之前一直使用C++开发，近期工作原因要使用C#语言，学习了一个月，感觉C#入门还是比C++容易些，而且网上
实例也相比C++多很多，但是在开发读写Excel功能时还是遇到很多坑。。。。
在此总结一下，以便大家跳过此坑！

本Demo在NPOI基础上开发，其特点是读写速度快！！！不受255列限制！！！

我们在开发的过程中可能会碰到这样的需求：读取.xlsx文件中的数据，经过一系列计算，再导出至.xls或者.xlsx中，或者在已经存在的文件中的某个位置追加数据，本文主要讲解此读，写，追加数据三个部分！

先贴代码:

/// <summary>
        /// 打印调试信息
        /// </summary>
        /// <param name="str">       打印的内容</param>
        /// <param name="debug">     是否是debug模式</param>
        /// <returns></returns>
        private void Trace(string str, bool debug = true)
        {
            if (debug)
                System.Diagnostics.Debug.WriteLine(str);
            else
                System.Diagnostics.Trace.WriteLine(str);
        }

        /// <summary>  
        /// 将excel导入到Datatable  
        /// </summary>  
        /// <param name="file_path">                excel完整路径</param>  
        /// <param name="start_row">                从第几行开始读</param>  
        /// <param name="sheet_num">                第几个Sheet，0表示第一个</param> 
        /// <returns>Datatable</returns> 
        public DataTable Excel_To_DataTable(string file_path, int start_row = 0, int sheet_num = 0)
        {
            DataTable data_table = new DataTable();
            FileStream fs = null;

            IWorkbook work_book = null;
            IRow row = null;
            ICell cell = null;

            try
            {
                using (fs = File.OpenRead(file_path))
                {
                    string[] temp = file_path.Split('.');

                    if (temp[temp.Count() - 1].Equals("xlsx"))              // 2007版本  
                        work_book = new XSSFWorkbook(fs);
                    else if (temp[temp.Count() - 1].Equals("xls"))          // 2003版本 
                        work_book = new HSSFWorkbook(fs);
                    else
                        return null;

                    ISheet sheet = work_book.GetSheetAt(sheet_num);         //读取sheet_num+1个Sheet

                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum;                    //获取总行数  

                        if (0 == rowCount)
                        {
                            return null;
                        }
                        else
                        {
                            IRow first_row = sheet.GetRow(0);               //获取第一行信息
                            int cell_count = first_row.LastCellNum;         //通过第一行信息获取总列数 

                            DataColumn data_col = null;                     //填充列
                            for (int i = first_row.FirstCellNum; i < cell_count; ++i)
                            {
                                data_col = new DataColumn("data_col" + (i + 1));
                                data_table.Columns.Add(data_col);
                            }

                            DataRow data_row = null;                        //填充行
                            for (int i = start_row; i <= rowCount; ++i)
                            {
                                row = sheet.GetRow(i);
                                if (row == null) continue;

                                data_row = data_table.NewRow();
                                for (int j = row.FirstCellNum; j < cell_count; ++j)
                                {
                                    cell = row.GetCell(j);
                                    if (cell == null)
                                    {
                                        data_row[j] = "";
                                    }
                                    else
                                    {//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                        switch (cell.CellType)
                                        {
                                            case CellType.Blank:
                                                data_row[j] = "";
                                                break;
                                            case CellType.Numeric:
                                                short format = cell.CellStyle.DataFormat;
                                                if (format == 14 || format == 31 || format == 57 || format == 58)//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                                    data_row[j] = cell.DateCellValue;
                                                else
                                                    data_row[j] = cell.NumericCellValue;
                                                break;
                                            case CellType.String:
                                                data_row[j] = cell.StringCellValue;
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                }
                                data_table.Rows.Add(data_row);
                            }
                        }
                    }
                }

                return data_table;
            }
            catch (Exception e)
            {
                if (fs != null)
                    fs.Close();

                Trace(e.Message);
                return null;
            }
        }

        /// <summary>
        /// 将DataTable转为二维数组
        /// </summary>
        /// <param name="dt"></param>
        /// <returns>二维数组</returns>
        public string[,] DataTable_To_Array(DataTable dt)
        {
            int i = 0;
            int row_counts = dt.Rows.Count;
            int cols_counts = dt.Columns.Count;
            string[,] r = new string[row_counts, cols_counts];
            foreach (System.Data.DataRow row in dt.Rows)
            {
                int j = 0;
                foreach (System.Data.DataColumn column in dt.Columns)
                {
                    r[i, j] = row[column.ColumnName].ToString();
                    j = j + 1;
                }
                i = i + 1;
            }
            return r;
        }

        private void button_读Excel_Click(object sender, EventArgs e)
        {
            DataTable dt =  Excel_To_DataTable("D:\\test.xlsx");

            string[,] content = DataTable_To_Array(dt);
            for (int i = 1; i < content.GetLength(1) + 1; i++)
            {
                listView_show.Columns.Add(content[0,i-1], 50, System.Windows.Forms.HorizontalAlignment.Center);
            }

            for (int i = 1; i < content.GetLength(0); i++)
            {
                ListViewItem lvi = new ListViewItem();

                lvi.Text = (i).ToString();

                for (int j = 1; j < content.GetLength(1); j++)
                {
                    lvi.SubItems.Add(content[i, j]);
                }

                listView_show.Items.Add(lvi);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            listView_show.View = System.Windows.Forms.View.Details;            //设置Style
            listView_show.FullRowSelect = true;
            listView_show.GridLines = true;                                    //设置网格线
        }

读Excel是操作Excel中最简单的一步，因为读操作只是将表中的数据取出，并不需要考虑写操作中的各种格式，颜色等问题。