NPOI，将Excel表的数据存放到DataTable中

来源：互联网发布：是故教然后知困编辑：程序博客网时间：2024/04/29 17:01

private void button1_Click(object sender, EventArgs e)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            using (FileStream file = new FileStream(@"C:\Users\admin\Desktop\SoSo\SoSo\ExcelToDataTable\xls\8.22.xls", FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            DataTable dt = GetTable(sheet);
            PrintDataTable(dt);
        }
        /// <summary>
        /// 将单元页数据存放至DataTable中
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public DataTable GetTable(ISheet sheet)
        {
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            HSSFRow row = (HSSFRow)rows.Current;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                //将第一列作为列表头
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        /// <summary>
        /// 打印DataTable中的内容
        /// </summary>
        /// <param name="dt"></param>
        public void PrintDataTable(DataTable dt)
        {
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string str = dt.Rows[i][0] + ";" + dt.Rows[i][1] + ";" + dt.Rows[i][2] + ";" + dt.Rows[i][3] + ";" + dt.Rows[i][4] + ";" + dt.Rows[i][5] + ";" + dt.Rows[i][6] + ";" + dt.Rows[i][7];
                    Console.WriteLine(str);
                }
            }
        }

0 0