//读取Excel中的内容 System.Data.DataTable GetDataFromExcelByCom(bool hasTitle = true) { //打开对话框 OpenFileDialog openFile = new OpenFileDialog(); openFile.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); openFile.Multiselect = false; if (openFile.ShowDialog() == DialogResult.Cancel) { return null; } var excelFilePath = openFile.FileName; try { load = new Importing_window(); load.Show(); //this.Cursor = System.Windows.Forms.Cursors.WaitCursor; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Sheets sheets; object oMissiong = System.Reflection.Missing.Value; Workbook workbook = null;//创建工作簿 System.Data.DataTable dt = new System.Data.DataTable(); try { if (app == null) { return null; } workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); sheets = workbook.Worksheets; //将数据读入到DataTable中 Worksheet worksheet = (Worksheet)sheets.get_Item(1);//读取第一张表 if (worksheet == null) { return null; } int iRowCount = worksheet.UsedRange.Rows.Count; int iColCount = worksheet.UsedRange.Columns.Count; //生成列头 for (int i = 0; i < iColCount; i++) { var name = "column" + i; if (hasTitle) { var txt = ((Range)worksheet.Cells[1, i + 1]).Text.ToString(); if (!string.IsNullOrEmpty(txt)) { name = txt; } } while (dt.Columns.Contains(name)) { name = name + "_1";//重复行名称会报错。 } dt.Columns.Add(new DataColumn(name, typeof(string))); } //生成行数据 Range range; int rowIdx = hasTitle ? 2 : 1; for (int iRow = rowIdx; iRow <= iRowCount; iRow++) { DataRow dr = dt.NewRow(); for (int iCol = 1; iCol <= iColCount; iCol++) { range = (Range)worksheet.Cells[iRow, iCol]; dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString(); } dt.Rows.Add(dr); } return dt; } catch { return null; } finally { workbook.Close(false, oMissiong, oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } } catch { Toolkit.MessageBox.Show("无法导入,可能您的机子Office版本有问题!", "系统提示", MessageBoxButton.OK, MessageBoxImage.Information); return null; } }
//读取Excel中的内容 System.Data.DataTable GetDataFromExcelByCom(bool hasTitle = true) { //打开对话框 OpenFileDialog openFile = new OpenFileDialog(); openFile.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); openFile.Multiselect = false; if (openFile.ShowDialog() == DialogResult.Cancel) { return(null); } var excelFilePath = openFile.FileName; try { load = new Importing_window(); load.Show(); //this.Cursor = System.Windows.Forms.Cursors.WaitCursor; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Sheets sheets; object oMissiong = System.Reflection.Missing.Value; Workbook workbook = null;//创建工作簿 System.Data.DataTable dt = new System.Data.DataTable(); try { if (app == null) { return(null); } workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); sheets = workbook.Worksheets; //将数据读入到DataTable中 Worksheet worksheet = (Worksheet)sheets.get_Item(1);//读取第一张表 if (worksheet == null) { return(null); } int iRowCount = worksheet.UsedRange.Rows.Count; int iColCount = worksheet.UsedRange.Columns.Count; //生成列头 for (int i = 0; i < iColCount; i++) { var name = "column" + i; if (hasTitle) { var txt = ((Range)worksheet.Cells[1, i + 1]).Text.ToString(); if (!string.IsNullOrEmpty(txt)) { name = txt; } } while (dt.Columns.Contains(name)) { name = name + "_1";//重复行名称会报错。 } dt.Columns.Add(new DataColumn(name, typeof(string))); } //生成行数据 Range range; int rowIdx = hasTitle ? 2 : 1; for (int iRow = rowIdx; iRow <= iRowCount; iRow++) { DataRow dr = dt.NewRow(); for (int iCol = 1; iCol <= iColCount; iCol++) { range = (Range)worksheet.Cells[iRow, iCol]; dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString(); } dt.Rows.Add(dr); } return(dt); } catch { return(null); } finally { workbook.Close(false, oMissiong, oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } } catch { Toolkit.MessageBox.Show("无法导入,可能您的机子Office版本有问题!", "系统提示", MessageBoxButton.OK, MessageBoxImage.Information); return(null); } }