- publicDataSet ImportExcel(string filePath)
- {
- DataSet ds =null;
- DataTable dt =null;
- Microsoft.Office.Interop.Excel.Application excel =new Microsoft.Office.Interop.Excel.Application();
- Microsoft.Office.Interop.Excel.Workbook workbook =null;
- Microsoft.Office.Interop.Excel.Worksheet worksheet =null;
- Microsoft.Office.Interop.Excel.Sheets sheets =null;
- Microsoft.Office.Interop.Excel.Range range =null;
- objectmissing = System.Reflection.Missing.Value;
- try
- {
- if(excel ==null)
- {
- return null;
- }
- //打开 Excel 文件workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
- //获取所有的 sheet 表sheets = workbook.Worksheets;
- ds =new DataSet();
- for(inti =1; i <= sheets.Count; i++)
- {
- //获取第一个表worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
- introwCount = worksheet.UsedRange.Rows.Count;
- intcolCount = worksheet.UsedRange.Columns.Count;
- introwIndex =1;//起始行为 1
- intcolIndex =1;//起始列为 1
- DataColumn dc;
- dt =new DataTable();
- dt.TableName ="table"+ i.ToString();
- //读取列名
- for(intj =0; j < colCount; j++)
- {
- range = worksheet.Cells[rowIndex, colIndex + j];
- dc =new DataColumn();
- dc.DataType = Type.GetType("System.String");
- dc.ColumnName = range.Text.ToString().Trim();
- //添加列
- dt.Columns.Add(dc);
- }
- //读取行数据
- for(intk =1; k < rowCount; k++)
- {
- DataRow dr = dt.NewRow();
- for(intl =0; l < colCount; l++)
- {
- range = worksheet.Cells[rowIndex + k, colIndex + l];
- //使用 range.Value.ToString(); 或 range.Value2.ToString(); 或 range.Text.ToString(); 都可以获取单元格的值dr[l] = range.Text.ToString();
- }
- dt.Rows.Add(dr.ItemArray);
- }
- ds.Tables.Add(dt);
- }
- }
- catch (Exception ex)
- {
- throw;
- }
- return ds;
- }
来源: http://www.bubuko.com/infodetail-2081565.html