• 读写Excel


    nmp安装NPOI

    读取Excel为DataTable

     1 public static DataTable DoSql(string url)
     2         {
     3             try
     4             {
     5                 //实例化DataTable来存放数据
     6                 DataTable dt = new DataTable();
     7                 //string fileName = "MyData.xlsx";
     8                 string fileName = url;
     9                 string sheetName = "sheet1";//Excel的工作表名称
    10                 bool isColumnName = true;//判断第一行是否为标题列
    11                 IWorkbook workbook;//创建一个工作薄接口
    12                 string fileExt = Path.GetExtension(fileName).ToLower();//获取文件的拓展名
    13                                                                        //创建一个文件流
    14                 using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
    15                 {
    16                     if (fileExt.ToLower() == ".xlsx")
    17                     {
    18                         workbook = new XSSFWorkbook(fs);
    19                     }
    20                     else
    21                     {
    22                         workbook = new HSSFWorkbook(fs); ;
    23                     }
    24 
    25                     //实例化sheet
    26                     ISheet sheet = null;
    27                     if (sheetName != null && sheetName != "")//判断是否存在sheet
    28                     {
    29                         sheet = workbook.GetSheet(sheetName);
    30                         if (sheet == null)
    31                         {
    32                             sheet = workbook.GetSheetAt(0);//从第一个开始读取,0位索引
    33                         }
    34                         else
    35                         {
    36                             sheet = workbook.GetSheetAt(0);
    37                         }
    38                     }
    39 
    40                     //获取表头
    41                     IRow header = sheet.GetRow(sheet.FirstRowNum);
    42                     int startRow = 0;//数据的第一行索引
    43                     if (isColumnName)//表示第一行是列名
    44                     {
    45                         startRow = sheet.FirstRowNum + 1;//数据从第二行开始读
    46 
    47                         //遍历表的第一行,即所有的列名
    48                         for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
    49                         {
    50                             ICell cell = header.GetCell(i);
    51                             if (cell != null)
    52                             {
    53                                 //获取列名的值
    54                                 string cellValue = cell.ToString();
    55                                 if (cellValue != null)
    56                                 {
    57                                     DataColumn col = new DataColumn(cellValue);
    58                                     dt.Columns.Add(col);
    59                                 }
    60                                 else
    61                                 {
    62                                     DataColumn col = new DataColumn();
    63                                     dt.Columns.Add(col);
    64                                 }
    65                             }
    66                         }
    67                     }
    68 
    69                     //读取数据
    70                     for (int i = startRow; i <= sheet.LastRowNum; i++)
    71                     {
    72                         IRow row = sheet.GetRow(i);
    73                         if (row == null)
    74                         {
    75                             continue;
    76                         }
    77                         DataRow dr = dt.NewRow();
    78                         for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
    79                         {
    80                             if (row.GetCell(j) != null)
    81                             {
    82                                 dr[j] = row.GetCell(j).ToString();
    83                             }
    84 
    85                         }
    86                         dt.Rows.Add(dr);
    87                     }
    88 
    89                 }
    90                 return dt;
    91             }
    92             catch (Exception)
    93             {
    94                 MessageBox.Show("打开失败,去找开发的");
    95                 return null;
    96             }
    97             
    98 
    99         }

    保存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;
                }
            }
  • 相关阅读:
    vue搭建开发环境
    一些意想不到的小bug。
    小程序开发中遇到的问题
    Per相关图书推荐
    MATLAB相关图书推荐
    CSS相关图书推荐
    Cocos2d相关图书推荐
    JSP相关图书推荐
    Fortran相关图书推荐
    R语言相关图书推荐
  • 原文地址:https://www.cnblogs.com/ThisYbc/p/16267534.html
Copyright © 2020-2023  润新知