C#中DataTable使用以及对行与列的赋值

爱被打了一巴掌 2024-04-17 05:59 47阅读 0赞

## 场景 ##

### DataTable ###

DataTable是一个临时保存数据的网格虚拟表(表示内存中数据的一个表)。

这里用来给DataGridView设置数据源。

### 效果 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

### 列属性 ###

也就是上面第一行显示的标题属性。

DataTable data = new DataTable();
    DataColumn column = new DataColumn(cellValue);
    data.Columns.Add(column);

### 行属性 ###

标题下面一行一行的属性。

DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                //同理，没有数据的单元格都默认是null
                                if (row.GetCell(j) != null)
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
    
                            data.Rows.Add(dataRow);

## 实现 ##

这里用读取Excel并赋值给DataTables然后设置DataGridView的数据源为例。

ISheet sheet = null;
                DataTable data = new DataTable();
                IWorkbook workbook = null;
                int startRow = 0;
                try
                {
    
                    //获取文件名，不带路径
                    string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
    
                    fs = new FileStream(localFilePath, FileMode.Open, FileAccess.Read);
                    // 2007版本
                    if (fileNameExt.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (fileNameExt.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
    
                    if (fileNameExt != null)
                    {
                        sheet = workbook.GetSheet("sheet1");
                        if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                        {
                            sheet = workbook.GetSheetAt(0);
                        }
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        //一行最后一个cell的编号 即总的列数
                        int cellCount = firstRow.LastCellNum;
    
                     
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                ICell cell = firstRow.GetCell(i);
                                if (cell != null)
                                {
                                    string cellValue = cell.StringCellValue;
                                    if (!string.IsNullOrEmpty(cellValue))
                                    {
                                        DataColumn column = new DataColumn(cellValue);
                                        data.Columns.Add(column);
                                    }
                                }
                            }
    
                         //excel表格中的第一行作为dataTable的标题
                         //所以从第二行开始
                        startRow = sheet.FirstRowNum+1;
                        //获取最后一行的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            //没有数据的行默认是null　　
                            if (row == null) continue; 　　　　　
    
                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                //同理，没有数据的单元格都默认是null
                                if (row.GetCell(j) != null)
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
    
                            data.Rows.Add(dataRow);
                        }
                    }
    
                  
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                  
                }
                //设置数据源
               
                this.dataGridView1.DataSource = data;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/15/0f5ce24c5a5a49c99792dcd0db08dd57.jpg