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);
}