static public bool ReadXlsData(string xlsPath, System.Data.DataTable DG, ReadXlsType readXlsType, ProgressBar progressBar) { //DataGridView DG = new DataGridView(); int ColumnsNum = 0; try { Excel.Application xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.ScreenUpdating = false; //打开Excel Excel.Workbook xlsWorkBook = xlApp.Workbooks.Open(xlsPath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //处理数据过程,更多操作方法自行百度 Excel.Worksheet sheet; if (xlsWorkBook.Sheets.Count > 1) { Excel.Worksheet sheetA = xlsWorkBook.Worksheets[1]; //工作薄从1开始,不是0 Excel.Worksheet sheetB = xlsWorkBook.Worksheets[2]; //工作薄从1开始,不是0 sheet = (sheetA.Cells.Count > sheetB.Cells.Count) ? sheetA : sheetB; } else { sheet = xlsWorkBook.Worksheets[1]; } int SheetCount = sheet.UsedRange.Rows.Count; if (SheetCount < 2) { MessageBox.Show("您选择的坐标XLS为空或首个Sheet表为空,请保证首个Sheet表为即将导入的坐标集!", "导入错误报告", MessageBoxButtons.OK); return(false); } if (progressBar != null) { progressBar.Minimum = 0; progressBar.Value = 2; progressBar.Maximum = SheetCount; progressBar.Step = 1; } switch (readXlsType) { case ReadXlsType.ReadWeldPoint: #region Read And Oprate WeldPoint Part RxTypeList.CatPointType CP = new RxTypeList.CatPointType(); bool ChangeGun = false; int GunNum = 0; int RowID = 1; for (int i = 1; i <= SheetCount; i++) { ColumnsNum = sheet.UsedRange.Columns.Count; if (progressBar != null) { progressBar.PerformStep(); } if (i == 1) //初始化表头 { if (ColumnsNum == 3 || ColumnsNum == 4 || ColumnsNum == 7 || ColumnsNum == 8 || ColumnsNum == 21) { System.Data.DataColumn dataColum; if (DG.Columns.Count < 1) { dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "序号"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "名称"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "X坐标"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "Y坐标"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "Z坐标"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "RX"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "RY"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "RZ"; DG.Columns.Add(dataColum); } } else { MessageBox.Show("所提供的EXCEL 文件格式不符合默认标准请提供4列(名称、X、Y、Z)、7列(带角度)、8列(多序号)的Xls表!"); return(false); } } else { string CheckValue = sheet.UsedRange.Cells[i, 1].Value; if (string.IsNullOrEmpty(CheckValue)) { continue; } try { switch (ColumnsNum) { case 3: { DG.Rows.Add(i, "RX_" + (i - 1), sheet.Cells[i, 1].Value, sheet.Cells[i, 2].Value, sheet.Cells[i, 3].Value, 0, 0, 0); break; } case 4: { DG.Rows.Add((i - 1), sheet.Cells[i, 1].Value, sheet.Cells[i, 2].Value, sheet.Cells[i, 3].Value, sheet.Cells[i, 4].Value, 0, 0, 0); break; } case 7: { if (string.IsNullOrEmpty(Convert.ToString(sheet.Cells[i, 1].Value)) || string.IsNullOrEmpty(Convert.ToString(sheet.Cells[i, 2].Value))) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add((i - 1), sheet.Cells[i, 1].Value, sheet.Cells[i, 2].Value, sheet.Cells[i, 3].Value, sheet.Cells[i, 4].Value, sheet.Cells[i, 5].Value, sheet.Cells[i, 6].Value, sheet.Cells[i, 7].Value); RowID++; break; } case 8: { if (string.IsNullOrEmpty(Convert.ToString(sheet.Cells[i, 1].Value)) || string.IsNullOrEmpty(Convert.ToString(sheet.Cells[i, 2].Value))) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add((i - 1), sheet.Cells[i, 2].Value, sheet.Cells[i, 3].Value, sheet.Cells[i, 4].Value, sheet.Cells[i, 5].Value, sheet.Cells[i, 6].Value, sheet.Cells[i, 7].Value, sheet.Cells[i, 8].Value); RowID++; break; } case 21: { if (string.IsNullOrEmpty(Convert.ToString(sheet.Cells[i, 1].Value)) || string.IsNullOrEmpty(Convert.ToString(sheet.Cells[i, 4].Value))) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add(RowID, sheet.Cells[i, 1].Value, sheet.Cells[i, 4].Value, sheet.Cells[i, 5].Value, sheet.Cells[i, 6].Value, sheet.Cells[i, 7].Value, sheet.Cells[i, 8].Value, sheet.Cells[i, 9].Value); RowID++; break; } default: break; } } catch (Exception) { throw; } } } ClosePro(xlApp, xlsWorkBook); return(true); #endregion case ReadXlsType.ReadRxWorkTime: #region Read Xls Data And Updata To DataGard For Rx WorkTime for (int i = 2; i <= SheetCount; i++) { if (progressBar != null) { progressBar.PerformStep(); } ColumnsNum = sheet.UsedRange.Columns.Count; if (i == 2) //初始化表头 { if (ColumnsNum == 3 || ColumnsNum == 4 || ColumnsNum == 7 || ColumnsNum == 8 || ColumnsNum == 21) { System.Data.DataColumn dataColum; if (DG.Columns.Count < 1) { dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "序号"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "人员编号"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "姓名"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "部门"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "上班考勤时间"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "下班考勤时间"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "考勤状态"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "考勤区域"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "设备序列号"; DG.Columns.Add(dataColum); } } else { MessageBox.Show("所提供的EXCEL 文件格式不符合默认标准请提供4列(名称、X、Y、Z)、7列(带角度)、8列(多序号)的Xls表!"); return(false); } } else { string CheckValue = sheet.UsedRange.Cells[i, 1].Value; if (string.IsNullOrEmpty(CheckValue)) { continue; } try { if (string.IsNullOrEmpty(sheet.Cells[i, 1].Value) || string.IsNullOrEmpty(sheet.Cells[i, 2].Value)) { ChangeGun = true; continue; } Task.Run(DG.Rows.Add((i - 2), sheet.Cells[i, 1].Value, sheet.Cells[i, 2].Value, sheet.Cells[i, 3].Value, sheet.Cells[i, 4].Value, 0, sheet.Cells[i, 5].Value, sheet.Cells[i, 6].Value, sheet.Cells[i, 7].Value)); //DG.Rows.Add((i - 2), sheet.Cells[i, 1].Value, sheet.Cells[i, 2].Value, sheet.Cells[i, 3].Value, sheet.Cells[i, 4].Value,0, sheet.Cells[i, 5].Value, sheet.Cells[i, 6].Value, sheet.Cells[i, 7].Value); //break; } catch (Exception) { throw; } } } ClosePro(xlApp, xlsWorkBook); #endregion break; default: break; } } catch (Exception) { MessageBox.Show("打开失败!使用该软件必须装载有Office 2013或以上版本,如果已安装请确认是否已解密?"); } return(false); }
static public bool ReadXlsData(string xlsPath, DataGridView DG) { //DataGridView DG = new DataGridView(); int RowNum = 0; try { IWorkbook xlsBook = WorkbookFactory.Create(xlsPath); ISheet sheet = (xlsBook.GetSheetAt(0).LastRowNum > xlsBook.GetSheetAt(1).LastRowNum) ? xlsBook.GetSheetAt(0) : xlsBook.GetSheetAt(1); RxTypeList.CatPointType CP = new RxTypeList.CatPointType(); bool ChangeGun = false; int GunNum = 0; int RowID = 1; for (int i = 0; i <= sheet.LastRowNum; i++) { if (sheet.LastRowNum < 2) { MessageBox.Show("您选择的坐标XLS为空或首个Sheet表为空,请保证首个Sheet表为即将导入的坐标集!", "导入错误报告", MessageBoxButtons.OK); return(false); } IRow Row = sheet.GetRow(i); RowNum = Row.LastCellNum; if (i == 0) //初始化表头 { if (RowNum == 3 || RowNum == 4 || RowNum == 7 || RowNum == 8 || RowNum == 21) { DG.DataSource = null; DG.AllowUserToAddRows = true; if (DG.ColumnCount > 0) { DG.Columns.Remove("序号"); DG.Columns.Remove("名称"); DG.Columns.Remove("X坐标"); DG.Columns.Remove("Y坐标"); DG.Columns.Remove("Z坐标"); DG.Columns.Remove("RX"); DG.Columns.Remove("RY"); DG.Columns.Remove("RZ"); } DG.Columns.Add("序号", "序号"); DG.Columns.Add("名称", "名称"); DG.Columns.Add("X坐标", "X坐标"); DG.Columns.Add("Y坐标", "Y坐标"); DG.Columns.Add("Z坐标", "Z坐标"); DG.Columns.Add("RX", "RX"); DG.Columns.Add("RY", "RY"); DG.Columns.Add("RZ", "RZ"); } else { MessageBox.Show("所提供的EXCEL 文件格式不符合默认标准请提供4列(名称、X、Y、Z)、7列(带角度)、8列(多序号)的Xls表!"); return(false); } } else { string CheckValue = Row.GetCell(0).StringCellValue; if (string.IsNullOrEmpty(CheckValue)) { continue; } try { switch (RowNum) { case 3: { DG.Rows.Add(i, "RX_" + i, Row.GetCell(0), Row.GetCell(1), Row.GetCell(2), 0, 0, 0); break; } case 4: { DG.Rows.Add(i, Row.GetCell(0), Row.GetCell(1), Row.GetCell(2), Row.GetCell(3), 0, 0, 0); break; } case 7: { if (string.IsNullOrEmpty(Row.GetCell(0).StringCellValue) || string.IsNullOrEmpty(Row.GetCell(1).ToString())) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add(i, Row.GetCell(0), Row.GetCell(1), Row.GetCell(2), Row.GetCell(3), Row.GetCell(4), Row.GetCell(5), Row.GetCell(6)); RowID++; break; } case 8: { if (string.IsNullOrEmpty(Row.GetCell(0).StringCellValue) || string.IsNullOrEmpty(Row.GetCell(2).ToString())) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add(i, Row.GetCell(1), Row.GetCell(2), Row.GetCell(3), Row.GetCell(4), Row.GetCell(5), Row.GetCell(6), Row.GetCell(7)); RowID++; break; } case 21: { if (string.IsNullOrEmpty(Row.GetCell(0).StringCellValue) || string.IsNullOrEmpty(Row.GetCell(3).ToString())) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add(RowID, Row.GetCell(0), Row.GetCell(3), Row.GetCell(4), Row.GetCell(5), Row.GetCell(6), Row.GetCell(7), Row.GetCell(8)); RowID++; break; } default: break; } } catch (Exception) { throw; } } } return(true); } catch (Exception) { MessageBox.Show("打开失败!请确认是否已解密?"); } return(false); }
static public bool ReadXlsData(string xlsPath, System.Data.DataTable DG) { //DataGridView DG = new DataGridView(); IWorkbook xlsBook = null; int RowNum = 0; try { xlsBook = WorkbookFactory.Create(xlsPath); } catch (Exception e) { ////FileUpload1是 一个FileUpload控件 //FileInfo fileInfo = new FileInfo(xlsPath); //Stream stream = File.OpenRead(xlsPath); //InputStream inputStream = new FileInputStream(stream); //MemoryStream m = new MemoryStream(); ////将文件的流拷贝一份,以内存流的格式保存 //CopyStream(inputStream, m); //if (fileInfo.Extension.ToLower().Equals(".xls")) //{ // xlsBook = new HSSFWorkbook(inputStream); //} //else //{ // xlsBook = WorkbookFactory.Create(inputStream); //} MessageBox.Show("打开失败!错误:" + e.Message.ToString()); return(false); } try { ISheet sheet = (xlsBook.GetSheetAt(0).LastRowNum > xlsBook.GetSheetAt(1).LastRowNum) ? xlsBook.GetSheetAt(0) : xlsBook.GetSheetAt(1); RxTypeList.CatPointType CP = new RxTypeList.CatPointType(); bool ChangeGun = false; int GunNum = 0; int RowID = 1; for (int i = 0; i <= sheet.LastRowNum; i++) { if (sheet.LastRowNum < 2) { MessageBox.Show("您选择的坐标XLS为空或首个Sheet表为空,请保证首个Sheet表为即将导入的坐标集!", "导入错误报告", MessageBoxButtons.OK); return(false); } IRow Row = sheet.GetRow(i); RowNum = Row.LastCellNum; if (i == 0) //初始化表头 { if (RowNum == 3 || RowNum == 4 || RowNum == 7 || RowNum == 8 || RowNum == 21) { System.Data.DataColumn dataColum; if (DG.Columns.Count < 1) { dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "序号"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "名称"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "X坐标"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "Y坐标"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "Z坐标"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "RX"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "RY"; DG.Columns.Add(dataColum); dataColum = new System.Data.DataColumn(); dataColum.ColumnName = "RZ"; DG.Columns.Add(dataColum); } } else { MessageBox.Show("所提供的EXCEL 文件格式不符合默认标准请提供4列(名称、X、Y、Z)、7列(带角度)、8列(多序号)的Xls表!"); return(false); } } else { string CheckValue = Row.GetCell(0).StringCellValue; if (string.IsNullOrEmpty(CheckValue)) { continue; } try { switch (RowNum) { case 3: { DG.Rows.Add(i, "RX_" + i, Row.GetCell(0), Row.GetCell(1), Row.GetCell(2), 0, 0, 0); break; } case 4: { DG.Rows.Add(i, Row.GetCell(0), Row.GetCell(1), Row.GetCell(2), Row.GetCell(3), 0, 0, 0); break; } case 7: { if (string.IsNullOrEmpty(Row.GetCell(0).StringCellValue) || string.IsNullOrEmpty(Row.GetCell(1).ToString())) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add(i, Row.GetCell(0), Row.GetCell(1), Row.GetCell(2), Row.GetCell(3), Row.GetCell(4), Row.GetCell(5), Row.GetCell(6)); RowID++; break; } case 8: { if (string.IsNullOrEmpty(Row.GetCell(0).StringCellValue) || string.IsNullOrEmpty(Row.GetCell(2).ToString())) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add(i, Row.GetCell(1), Row.GetCell(2), Row.GetCell(3), Row.GetCell(4), Row.GetCell(5), Row.GetCell(6), Row.GetCell(7)); RowID++; break; } case 21: { if (string.IsNullOrEmpty(Row.GetCell(0).StringCellValue) || string.IsNullOrEmpty(Row.GetCell(3).ToString())) { ChangeGun = true; continue; } if (ChangeGun) { ChangeGun = false; DG.Rows.Add(0, "ChangeGun", 0, 0, 0, 0, 0); GunNum++; } DG.Rows.Add(RowID, Row.GetCell(0), Row.GetCell(3), Row.GetCell(4), Row.GetCell(5), Row.GetCell(6), Row.GetCell(7), Row.GetCell(8)); RowID++; break; } default: break; } } catch (Exception) { throw; } } } return(true); } catch (Exception) { MessageBox.Show("打开失败!请确认是否已解密?"); } return(false); }