读写Excel

nmp安装NPOI

读取Excel为DataTable

public static DataTable DoSql(string url)
        {
            try
            {
                //实例化DataTable来存放数据
                DataTable dt = new DataTable();
                //string fileName = "MyData.xlsx";
                string fileName = url;
                string sheetName = "sheet1";//Excel的工作表名称
                bool isColumnName = true;//判断第一行是否为标题列
                IWorkbook workbook;//创建一个工作薄接口
                string fileExt = Path.GetExtension(fileName).ToLower();//获取文件的拓展名
                                                                       //创建一个文件流
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    if (fileExt.ToLower() == ".xlsx")
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fs); ;
                    }

                    //实例化sheet
                    ISheet sheet = null;
                    if (sheetName != null && sheetName != "")//判断是否存在sheet
                    {
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null)
                        {
                            sheet = workbook.GetSheetAt(0);//从第一个开始读取，0位索引
                        }
                        else
                        {
                            sheet = workbook.GetSheetAt(0);
                        }
                    }

                    //获取表头
                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    int startRow = 0;//数据的第一行索引
                    if (isColumnName)//表示第一行是列名
                    {
                        startRow = sheet.FirstRowNum + 1;//数据从第二行开始读

                        //遍历表的第一行，即所有的列名
                        for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
                        {
                            ICell cell = header.GetCell(i);
                            if (cell != null)
                            {
                                //获取列名的值
                                string cellValue = cell.ToString();
                                if (cellValue != null)
                                {
                                    DataColumn col = new DataColumn(cellValue);
                                    dt.Columns.Add(col);
                                }
                                else
                                {
                                    DataColumn col = new DataColumn();
                                    dt.Columns.Add(col);
                                }
                            }
                        }
                    }

                    //读取数据
                    for (int i = startRow; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }
                        DataRow dr = dt.NewRow();
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dr[j] = row.GetCell(j).ToString();
                            }

                        }
                        dt.Rows.Add(dr);
                    }

                }
                return dt;
            }
            catch (Exception)
            {
                MessageBox.Show("打开失败，去找开发的");
                return null;
            }
            

        }

保存DataTable为Excel

public static bool DataTableToExcel(DataTable dt,string url)
        {
            string filepath = url;
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    workbook = new HSSFWorkbook();
                    sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表  
                    int rowCount = dt.Rows.Count;//行数  
                    int columnCount = dt.Columns.Count;//列数  

                    int cellnum;

                    //设置列头  
                    row = sheet.CreateRow(0);//excel第一行设为列头  
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                    }

                    //设置每行每列的单元格,  
                    for (int i = 0; i < rowCount; i++)
                    {
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            cell = row.CreateCell(j);//excel第二行开始写入数据
                            //cell.SetCellValue(dt.Rows[i][j].ToString());

                            //保存单元格格式为数字
                            if (j < 2)
                            {
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                            else
                            {
                                //cell.SetCellValue(int.Parse(dt.Rows[i][j].ToString()));
                                if (dt.Rows[i][j] is DBNull)
                                {
                                    cell.SetCellValue(dt.Rows[i][j].ToString());
                                }
                                else
                                {
                                    cellnum = Convert.ToInt32(dt.Rows[i][j].ToString());
                                    cell.SetCellValue(cellnum);
                                }
                            }
                        }
                    }
                    if (System.IO.File.Exists(filepath))
                    {
                        File.Delete(filepath);
                        //if (MessageBox.Show("该文件已存在！确定覆盖吗？", "WARNING", MessageBoxButtons.OKCancel) == DialogResult.OK)
                        //{
                        //    File.Delete(filepath);
                        //}
                        //else
                        //{
                        //    return false;
                        //}

                    }
                    using (fs = File.OpenWrite(filepath))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                if (fs != null)
                {
                    fs.Close();
                }
                return false;
            }
        }