/// <summary> /// IQC 导出Excel 数据流 /// </summary> /// <param name="dataSource">数据源</param> /// <returns></returns> public System.IO.MemoryStream ExportPrintToExcel(List <SampleItemsIqcRecordModel> dataSource) { try { //数据为Null时返回数值 System.IO.MemoryStream stream = new System.IO.MemoryStream(); if (dataSource == null || dataSource.Count == 0) { return(stream); } string filePath = System.IO.Path.GetFullPath(PrintSampleModel(dataSource)); NPOI.HSSF.UserModel.HSSFWorkbook workbook = InitializeWorkbook(filePath); if (workbook == null) { return(null); } NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); sheet.ForceFormulaRecalculation = true; //保存 Store(dataSource); return(stream); } catch (Exception ex) { throw new Exception(ex.ToString()); } }
private static DataSet ExcelToTable(string fileName) { DataSet ds = new DataSet(); DataTable dt = null; FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new DataTable(sheet.SheetName); for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = 1; i <= sheet.LastRowNum; i++) { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { if (row.GetCell(j) != null) { sheet.GetRow(i).GetCell(j).SetCellType(NPOI.SS.UserModel.CellType.String); newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } } ds.Tables.Add(dt); } return(ds); }
private static DataTable ExcelInput(string FilePath) { //第一行一般为标题行。 DataTable table = new DataTable(); //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档,然后关闭文件 FileStream fsRead = File.OpenRead(FilePath); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead); fsRead.Close(); //获取excel的第一个sheet NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); //获取Excel的最大行数 int rowsCount = sheet.PhysicalNumberOfRows; //为保证Table布局与Excel一样,这里应该取所有行中的最大列数(需要遍历整个Sheet)。 //为少一交全Excel遍历,提高性能,我们可以人为把第0行的列数调整至所有行中的最大列数。 //int colsCount = sheet.GetRow(1).PhysicalNumberOfCells; //for (int i = 0; i < colsCount; i++) //{ // table.Columns.Add(i.ToString()); //} //for (int x = 0; x < rowsCount; x++) //{ // DataRow dr = table.NewRow(); // for (int y = 0; y < colsCount; y++) // { // dr[y] = sheet.GetRow(x).GetCell(y).ToString(); // } // table.Rows.Add(dr); //} //dataGridView标题 table.Columns.Add("原始值"); table.Columns.Add("修正值"); //取模板中第6列,从第二行开始循环取值 for (int x = 2; x < rowsCount; x++) { DataRow dr = table.NewRow(); dr[0] = sheet.GetRow(x).GetCell(6).ToString(); //第二列填入修改后的值 dr[1] = getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32); table.Rows.Add(dr); } sheet = null; workbook = null; return(table); }
/// <summary> /// LoadFromTamplateFile /// </summary> /// <param name="FileName"></param> /// <returns></returns> public Boolean LoadFromTamplateFile(string FileName) { using (System.IO.FileStream fs = System.IO.File.OpenRead(FileName)) //打开myxls.xls文件 { Book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); if (Book.NumberOfSheets > 0) { DefaultSheet = Book.GetSheetAt(0); } else { DefaultSheet = Book.CreateSheet("Sheet1"); } } return(true); }
/// <summary> /// Read an xls file from the specified path /// </summary> /// <param name="path">The path to the xls file</param> public static List <List <string> > Read(string path) { var returnValues = new List <List <string> >(); try { NPOI.SS.UserModel.IWorkbook book; using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read)) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(stream); } NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0); for (int rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum; rowIndex++) { var row = sheet.GetRow(rowIndex); List <string> currentRowList = new List <string>(); for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++) { var cell = row.GetCell(cellIndex); if (cell != null) { currentRowList.Add(cell.StringCellValue); } else { currentRowList.Add(string.Empty); } } returnValues.Add(currentRowList); } } catch (Exception ex) { UnityEngine.Debug.LogError("Не удалось прочитать файл xls по пути: " + path + ", Ошибка: " + ex.Message); } return(returnValues); }
private void buttonUpdateAndSaveFile_Click(object sender, EventArgs e) { if (!datasource.Equals("") && !excelsource.Equals("")) { //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档,然后关闭文件 FileStream fsRead = File.OpenRead(excelsource); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead); fsRead.Close(); //获取excel的第一个sheet NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); //获取Excel的最大行数 int rowsCount = sheet.PhysicalNumberOfRows; for (int x = 2; x < rowsCount; x++) { sheet.GetRow(x).GetCell(6).SetCellValue(getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32)); } //把编辑过后的工作薄重新保存为excel文件 SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "All files (*.*)|*.*|xls files (*.xls)|*.xls"; dialog.FilterIndex = 2; dialog.RestoreDirectory = true; dialog.ShowDialog(); if (!string.IsNullOrEmpty(dialog.FileName)) { FileStream fsWrite = File.Create(@dialog.FileName); workbook.Write(fsWrite); fsWrite.Close(); } } else { MessageBox.Show("都不加载下数据就搞?重头来过!"); datasource = ""; excelsource = ""; textBoxExcelUrl.Text = ""; textBoxDbUrl.Text = ""; } }
/// <summary> /// 导入excel /// </summary> /// <param name="file"></param> /// <returns></returns> public ActionResult ImportExcel(HttpPostedFileBase file) { try { //思路:获取读取的文件,把文件转换为二进制数组,然后转成内存流,利用NPOI把内存流中的数据读取成Excel Session.Remove("ImportExcel"); //把session中的ImportExcel移除避免残留以前数据 string fileExtension = System.IO.Path.GetExtension(file.FileName); //读取路径文件的扩展名 if (".xls".Equals(fileExtension) || ".XLS".Equals(fileExtension)) //判断读取的文件是.xls文件 { byte[] fileBytes = new byte[file.ContentLength]; //指定数组的长度获取Excel数据的大小 file.InputStream.Read(fileBytes, 0, file.ContentLength); //读取文件内容 // 转为 内存流 System.IO.MemoryStream excelFileStream = new System.IO.MemoryStream(fileBytes); //将内存流转为 工作簿 NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelFileStream); //判断工作簿中的工作表(Sheet)的个数 if (workbook.NumberOfSheets > 0) { //查询出 学院,专业,年级,班级 的信息:目的是用来查看导入的数据是否有重复 List <PatientVo> lsitStudentVos = new List <PatientVo>(); // 获取第一个工作表 NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); //PhysicalNumberOfRows 获取的是物理行数,也就是不包括那些空行(隔行)的情况。 //判断 工作表(sheet)中有数据 if (sheet.PhysicalNumberOfRows > 0) { //将数据先装到datatable中 // 定义datatable DataTable dtExcel = new DataTable(); //获取标题行 第一行 NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); //获取一行单元格个数 LastCellNum 获取列数,比最后一列列标大 1 int cellCount = headerRow.LastCellNum; //获取数据总行数 LastRowNum 最后一行行标,比行数小 1 int rowCount = sheet.LastRowNum + 1; //创建DataTable的列Columns for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); dtExcel.Columns.Add(column); } //读取Excel中的数据 //(sheet.FirstRowNum) 第一行是标题 for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //获取行 DataRow dataRow = dtExcel.NewRow(); //DataTable创建一行 if (row != null) { //遍历Excel一行的所有单元格 for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } } //添加行DataRow到DataTable dtExcel.Rows.Add(dataRow); } //遍历datatable 获取数据 foreach (DataRow row in dtExcel.Rows) { //创建一个 StudentVo的对象 PatientVo student = new PatientVo(); try { //获取性别ID var dd = row["性别"].ToString().Trim(); var SexID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == dd select tbSex.BaseDetailID).Single(); student.sexID = Convert.ToInt32(SexID); student.Sex = row["性别"].ToString().Trim(); //病人所属ID var PatientOwnership = row["病人所属"].ToString().Trim(); var cantonID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == PatientOwnership select tbSex.BaseDetailID).Single(); student.cantonID = Convert.ToInt32(cantonID); student.PatientOwnership = row["病人所属"].ToString().Trim(); //获取人群分类id和名称 var CrowdClass = row["人群分类"].ToString().Trim(); var CrowdclassificationID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CrowdClass select tbSex.BaseDetailID).Single(); student.CrowdclassificationID = Convert.ToInt32(CrowdclassificationID); student.CrowdClass = row["人群分类"].ToString().Trim(); //获取病例分类ID和名称 var CaseClass = row["病例分类"].ToString().Trim(); var CaseClassID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CaseClass select tbSex.BaseDetailID).Single(); student.CaseClassID = Convert.ToInt32(CaseClassID); student.CaseClass = row["病例分类"].ToString().Trim(); //获取病例名称ID和名称 var DiseaseType = row["疾病名称类型"].ToString().Trim(); var DiseaseTypeID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == DiseaseType select tbSex.BaseDetailID).Single(); student.DiseaseTypeID = Convert.ToInt32(DiseaseTypeID); student.DiseaseType = row["疾病名称类型"].ToString().Trim(); //获取审核状态ID和名称 var AuditStatu = row["审核状态"].ToString().Trim(); var AuditStatusID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == AuditStatu select tbSex.BaseDetailID).Single(); student.AuditStatusID = Convert.ToInt32(AuditStatusID); student.AuditStatus = row["审核状态"].ToString().Trim(); //给创建实体赋值 student.Cardnumber = row["卡片编号"].ToString().Trim(); student.name = row["患者姓名"].ToString().Trim(); student.IDnumber = row["身份证号码"].ToString().Trim(); student.BirthDateTime = row["出生日期"].ToString().Trim(); student.workunit = row["工作单位"].ToString().Trim(); student.Addressnationalstandard = row["现详细住址国标"].ToString().Trim(); student.Detailedaddress = row["现住详细地址"].ToString().Trim(); student.CensusAddressInter = row["户籍地址国标"].ToString().Trim(); student.CensusAddDetail = row["户籍地址详细"].ToString().Trim(); student.MorbidityDateTime = row["发病日期"].ToString().Trim(); student.DiagnoseDateTime = row["诊断日期"].ToString().Trim(); student.ReportDateTime = row["录入日期"].ToString().Trim(); student.RevisedDateTime = row["订正终审日期"].ToString().Trim(); student.Occupation = row["职业"].ToString().Trim(); student.Remark = row["备注"].ToString().Trim(); lsitStudentVos.Add(student); } catch (Exception e) { Console.WriteLine(e); } } //把数据存在session当中做临时保存,这里还没有保存到数据库 Session["ImportExcel"] = lsitStudentVos; return(Json(true, JsonRequestBehavior.AllowGet)); } else { //物理行数为0 } } else { //没有工作表 } } else { //上传的文件类型不正确 } } catch (Exception e) { Console.WriteLine(e); } return(Json(false, JsonRequestBehavior.AllowGet)); }
public Form_PurchaseOrderImpt() { InitializeComponent(); #region 绑定类型 var ordertypes = EnumToListHelper.ConverEnumToList(typeof(PurchaseDrugTypes)).Where(r => r.Name != "食品").ToList(); this.toolStripComboBox1.ComboBox.DisplayMember = "Name"; this.toolStripComboBox1.ComboBox.ValueMember = "Id"; this.toolStripComboBox1.ComboBox.DataSource = ordertypes; #endregion #region DataGridView初始化 this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; this.dataGridView1.RowPostPaint += (s, e) => DataGridViewOperator.SetRowNumber(this.dataGridView1, e); this.dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; this.dataGridView2.RowPostPaint += (s, e) => DataGridViewOperator.SetRowNumber(this.dataGridView2, e); #endregion #region 右键菜单 BugsBox.Pharmacy.UI.Common.BaseRightMenu brm = new BugsBox.Pharmacy.UI.Common.BaseRightMenu(this.dataGridView1); #endregion #region 清理表格数据 Action ClearData = () => { this.ListDetails.Clear(); this.dataGridView1.DataSource = null; this.ListDetailsWaitingImpt.Clear(); this.dataGridView2.DataSource = null; }; #endregion #region 打开EXCEL文件 this.toolStripButton1.Click += (s, e) => { OpenFileDialog ofd = new OpenFileDialog { Filter = "XLS文件|*.xls|XLSX文件|*.xlsx", }; var re = ofd.ShowDialog(); if (re != System.Windows.Forms.DialogResult.OK) { return; } FileStream fs = new FileStream(ofd.FileName, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0); #region 简单验证一下excel表格 if (sheet == null) { MessageBox.Show("模板文件出错,请检查!"); return; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { MessageBox.Show("模板文件出错,请检查!"); return; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { MessageBox.Show("模板文件出错,请检查!"); return; } #endregion ClearData(); for (int i = 1; i < sheet.LastRowNum + 1; i++) { var sheetrow = sheet.GetRow(i); Business.Models.PurchaseOrderImpt m = new Business.Models.PurchaseOrderImpt(); m.ProductGeneralName = sheet.GetRow(i).Cells[0].StringCellValue; m.DosageName = sheet.GetRow(i).Cells[1].StringCellValue; m.SpecificName = sheet.GetRow(i).Cells[2].StringCellValue; m.MeasurementName = sheet.GetRow(i).Cells[3].StringCellValue; m.FactoryName = sheet.GetRow(i).Cells[4].StringCellValue; m.Origin = sheet.GetRow(i).Cells[5].StringCellValue; m.Amount = decimal.Parse(sheet.GetRow(i).Cells[6].NumericCellValue.ToString()); m.UnitPrice = decimal.Parse(sheet.GetRow(i).Cells[7].NumericCellValue.ToString()); m.TaxRate = decimal.Parse(sheet.GetRow(i).Cells[8].NumericCellValue.ToString()); this.ListDetails.Add(m); } this.dataGridView1.DataSource = this.ListDetails; this.dataGridView1.Columns["DruginfoId"].Visible = false; }; #endregion #region 生成模板文件 this.toolStripButton4.Click += (s, e) => { DownlodExcel(); MessageBox.Show("导出成功!"); }; #endregion #region 务器端验证 this.toolStripButton2.Click += (s, e) => { if (this.dataGridView1.Rows.Count <= 0) { return; } var result = this.PharmacyDatabaseService.CheckForPurchaseOrderDetails(this.ListDetails, out msg).ToList(); if (result.Any(r => r.DrugInfoId == Guid.Empty)) { MessageBox.Show("有一个或多个记录没有验证成功,请检查品名,剂型,规格等基本信息!您可以修改后再尝试验证!"); } this.ListDetails = result.Where(r => r.DrugInfoId == Guid.Empty).ToList(); this.dataGridView1.DataSource = ListDetails; this.ListDetailsWaitingImpt = result.Where(r => r.DrugInfoId != Guid.Empty).ToList(); this.dataGridView2.DataSource = this.ListDetailsWaitingImpt; this.dataGridView2.Columns["DrugInfoId"].Visible = false; this.dataGridView2.Refresh(); }; #endregion #region 导入按钮click this.toolStripButton3.Click += (s, e) => { if (this.ListDetailsWaitingImpt.Count <= 0) { return; } if (this.OnPurchaseOrderImpt != null) { PurchaseOrderImptEventArgs args = new PurchaseOrderImptEventArgs { ImptList = this.ListDetailsWaitingImpt }; this.OnPurchaseOrderImpt(args); } }; #endregion }
public static DataSet parse(string ExcelFilePath, string ZipPassword, string FirstColumnName) { //OleDbConnection cnnxls = null; DataSet ds = new DataSet(); if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".zip", StringComparison.CurrentCultureIgnoreCase)) { string strTmpFolder = ExcelFilePath + ".dir"; try { zip.ExtractAll(ExcelFilePath, strTmpFolder, ZipPassword); System.IO.DirectoryInfo rootDir = new System.IO.DirectoryInfo(strTmpFolder); foreach (System.IO.FileInfo fileInfo in rootDir.GetFiles("*", System.IO.SearchOption.AllDirectories)) { ds.Merge(parse(fileInfo.FullName, ZipPassword, FirstColumnName)); } } catch (Exception ex) { throw ex; } finally { System.IO.Directory.Delete(strTmpFolder, true); } //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFolder + ";Extended Properties=\"text;HDR=YES;IMEX=1;FMT=Delimited;\""; //cnnxls = new OleDbConnection(strConn); //cnnxls.Open(); } else if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".csv", StringComparison.CurrentCultureIgnoreCase)) { System.IO.FileInfo fileInfo = new System.IO.FileInfo(ExcelFilePath); DataTable table = CSVReader.parse(fileInfo.OpenRead(), true, ",", "\""); table.TableName = System.IO.Path.GetFileNameWithoutExtension(fileInfo.FullName); ds.Tables.Add(table); } else { NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(ExcelFilePath, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename); for (int sheetIndex = 0; sheetIndex < workBook.NumberOfSheets; sheetIndex++) { if (!workBook.IsSheetHidden(sheetIndex)) { int intHeaderRow = 0; NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(sheetIndex); NPOI.HSSF.UserModel.HSSFRow headerRow = null; //= (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow); if (!string.IsNullOrEmpty(FirstColumnName)) { for (int tmpRowIdx = intHeaderRow; tmpRowIdx <= workSheet.LastRowNum; tmpRowIdx++) { headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(tmpRowIdx); if (headerRow == null) { continue; } bool columnNameMatch = false; for (int tmpColumnIndex = 0; tmpColumnIndex <= headerRow.LastCellNum; tmpColumnIndex++) { if (headerRow.GetCell(tmpColumnIndex) != null) { string columnName = headerRow.GetCell(tmpColumnIndex).ToString().Trim(); if (FirstColumnName.Equals(columnName)) { intHeaderRow = tmpRowIdx; columnNameMatch = true; break; } } } if (columnNameMatch) { break; } } } else { headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow); } if (headerRow == null) { continue; } string tableName = workSheet.SheetName.Trim(); DataTable table = new DataTable(tableName); int intColumnIndex = 0; while (intColumnIndex <= headerRow.LastCellNum) { if (headerRow.GetCell(intColumnIndex) != null) { string columnName = headerRow.GetCell(intColumnIndex).ToString().Trim(); if (string.IsNullOrEmpty(columnName)) { columnName = "Column_" + intColumnIndex; } if (table.Columns.Contains(columnName)) { columnName = "Column_" + intColumnIndex; } table.Columns.Add(columnName, typeof(string)); // resign new value of column name to Excel for below part of import headerRow.GetCell(intColumnIndex).SetCellValue(columnName); } intColumnIndex++; } int rowCount = 1; while (intHeaderRow + rowCount <= workSheet.LastRowNum) { int colCount = 0; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow + rowCount); if (row == null) { rowCount++; continue; } DataRow dataRow = table.NewRow(); while (colCount <= headerRow.LastCellNum) { if (headerRow.GetCell(colCount) != null) { string columnName = headerRow.GetCell(colCount).ToString(); if (table.Columns.Contains(columnName)) { NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(colCount); if (cell != null) { if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.FORMULA)) { NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workBook); cell = (NPOI.HSSF.UserModel.HSSFCell)e.EvaluateInCell(cell); } string fieldValue = cell.ToString(); if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { string format = string.Empty; //bool IsBuildinformat = false; // Not sure whether workBook.CreateDataFormat().GetFormat(index) can obtain all the build-in format try { format = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat(cell.CellStyle.DataFormat); //IsBuildinformat = true; } catch { format = workBook.CreateDataFormat().GetFormat(cell.CellStyle.DataFormat); } // [h]:mm:ss handle NOT support int midBlanketStartPos = format.IndexOf('['); while (midBlanketStartPos >= 0) { int midBlanketEndPos = format.IndexOf(']', midBlanketStartPos); format = format.Substring(0, midBlanketStartPos) + format.Substring(midBlanketStartPos + 1, midBlanketEndPos - midBlanketStartPos - 1) + format.Substring(midBlanketEndPos + 1); midBlanketStartPos = format.IndexOf('['); } if (format.IndexOf("y", StringComparison.CurrentCultureIgnoreCase) >= 0 || format.IndexOf("d", StringComparison.CurrentCultureIgnoreCase) >= 0) { if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0) { fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { DateTime date = cell.DateCellValue; if (date.TimeOfDay.TotalSeconds > 0) { fieldValue = date.ToString("yyyy-MM-dd HH:mm:ss"); } else { fieldValue = date.ToString("yyyy-MM-dd"); } } } else if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0) { DateTime date = cell.DateCellValue; // default date of "Time Only" field is 1899-12-31 if (!date.Date.Ticks.Equals(new DateTime(1899, 12, 31).Ticks)) { fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { fieldValue = cell.DateCellValue.ToString("HH:mm:ss"); } } else { fieldValue = cell.NumericCellValue.ToString(); } } dataRow[columnName] = fieldValue; } } } colCount++; } table.Rows.Add(dataRow); rowCount++; } ds.Tables.Add(table); } } //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\""; //cnnxls = new OleDbConnection(strConn); //try //{ // cnnxls.Open(); //} //catch //{ // cnnxls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\""; // cnnxls.Open(); //} //DataTable schemaTable = cnnxls.GetSchema("Tables"); //foreach (DataRow schemaRow in schemaTable.Rows) //{ // string tableName = schemaRow["Table_Name"].ToString().Trim(); // if (tableName.EndsWith("$")) // { // OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", cnnxls); // try // { // //DataTable[] tables = oda.FillSchema(ds, SchemaType.Mapped);// // //tables[0].TableName = schemaRow["Table_Name"].ToString().Replace("$", "").Replace("#csv", ""); // //if (tables[0].Columns.Contains("Emp No*")) // // tables[0].Columns["Emp No*"].DataType = typeof(string); // //OleDbDataReader dr = oda.SelectCommand.ExecuteReader(); // //while (dr.Read()) // //{ // // DataRow row = tables[0].NewRow(); // // for (int i = 0; i < tables[0].Columns.Count; i++) // // row[i] = dr[i]; // // tables[0].Rows.Add(row); // //} // //// oda.Fill(tables[0]); // //if (ds.Tables.Contains(tableName) && tableName.ToString().EndsWith("$")) // // ds.Tables.Remove(tableName); // string actualTableName = tableName.Substring(0, tableName.Length - 1); // if (!ds.Tables.Contains(actualTableName)) // oda.Fill(ds, actualTableName); // } // catch // { // // unknown error caused by hidden sheet // } // // oda.Fill(ds); // } //} //cnnxls.Close(); } foreach (DataTable tempTable in ds.Tables) { for (int rowIdx = tempTable.Rows.Count - 1; rowIdx >= 0; rowIdx--) { DataRow row = tempTable.Rows[rowIdx]; bool isEmptyRow = true; foreach (DataColumn tempColumn in tempTable.Columns) { if (!row.IsNull(tempColumn)) { if (!string.IsNullOrEmpty(row[tempColumn].ToString().Trim())) { isEmptyRow = false; break; } } } if (isEmptyRow) { tempTable.Rows.Remove(row); } else { break; } } } foreach (DataTable tempTable in ds.Tables) { foreach (DataColumn tempColumn in tempTable.Columns) { string tempColumnName = tempColumn.ColumnName; tempColumnName = tempColumnName.Trim().Replace("*", ""); tempColumnName = tempColumnName.Trim().Replace("#", ""); tempColumn.ColumnName = tempColumnName; } } return(ds); }
public override FileInfo GenerateBankFile() { NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(System.IO.File.OpenRead(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ICBCBankFile.xls"))); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); int rowCount = 10; foreach (GenericBankFileDetail bankFileDetail in BankFileDetails) { NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowCount); if (row == null) { row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowCount); } NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(0); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0); } cell.SetCellValue(bankFileDetail.EmpNo); cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(1); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1); } cell.SetCellValue(bankFileDetail.EmpBankAccountHolderName); cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(2); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2); } cell.SetCellValue(bankFileDetail.BankCode + bankFileDetail.BranchCode + bankFileDetail.AccountNo); cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(3); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3); } cell.SetCellValue(bankFileDetail.Amount); rowCount++; } string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workBook.Write(file); file.Close(); return(new FileInfo(exportFileName)); }
private void import_bt_Click(object sender, EventArgs e) { //string filepath = ""; //OpenFileDialog opf = new OpenFileDialog(); //if (opf.ShowDialog() == DialogResult.OK) //{ // filepath = opf.FileName; //} DataSet ds = new DataSet(); System.Data.DataTable dt = null; OpenFileDialog sflg = new OpenFileDialog(); sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { string st_name = book.GetSheetName(sheetIndex); NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new System.Data.DataTable(sheet.SheetName); dt.Columns.Add("PN", typeof(string)); //MessageBox.Show(dt.Columns["bushe_xianshu"].DataType.ToString()); //dt.Columns.Add("", typeof(int)); dt.Columns.Add("name", typeof(string)); dt.Columns.Add("jobnum", typeof(string)); dt.Columns.Add("ARef", typeof(string)); dt.Columns.Add("size", typeof(string)); dt.Columns.Add("sm", typeof(string)); dt.Columns.Add("Barcode", typeof(string)); lastCellNum = 7; for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = 1; i <= sheet.LastRowNum; i++) { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } NPOI.SS.UserModel.IRow row0 = sheet.GetRow(0); ds.Tables.Add(dt); main_gc.DataSource = ds.Tables[0]; } for (int i = 0; i < gridView1.RowCount; i++) { string LJH = gridView1.GetRowCellValue(i, "PN").ToString(); string mc = gridView1.GetRowCellValue(i, "name").ToString(); string gdh = gridView1.GetRowCellValue(i, "jobnum").ToString(); string BH = gridView1.GetRowCellValue(i, "ARef").ToString(); string cc = gridView1.GetRowCellValue(i, "size").ToString(); string dsm = gridView1.GetRowCellValue(i, "sm").ToString(); string tm = gridView1.GetRowCellValue(i, "Barcode").ToString(); Maticsoft.BLL.parts use = new Maticsoft.BLL.parts(); Maticsoft.Model.parts us = new parts() { PN = LJH, //name = mc, //jobnum = gdh, //ARef = BH, //size = cc, //sm = dsm, Barcode = tm, }; use.Add(us); } DevExpress.XtraEditors.XtraMessageBox.Show("导入成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); //MessageBox.Show("已成功导入"); Maticsoft.BLL.parts pr = new Maticsoft.BLL.parts(); DataSet ds2 = pr.GetAllList(); main_gc.DataSource = ds2.Tables[0]; }
/// <summary> /// 导出Excel /// </summary> /// <param name="dt">DataTable:要导出的数据源</param> /// <param name="excelFilePath">string:文件的物理路径</param> /// <param name="isCustomHead">bool:是否自定义表头</param> /// <param name="tbColumnNames">List:表头</param> /// <param name="sheetName">string:sheet表名</param> /// <returns></returns> public static bool Output(DataTable dt, string excelFilePath, bool isCustomHead = false, List <string> tbColumnNames = null, string sheetName = "", Action <Exception> errorFun = null, int sheetIndex = 0) { if (!System.IO.File.Exists(excelFilePath)) { throw new Exception("Excel 文件不存在"); } if (null == dt && dt.Rows.Count == 0) { return(false); } try { //1.0 创建工作薄 和 工作表对象 NPOI.HSSF.UserModel.HSSFWorkbook book; using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite)) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile); } sheetName = string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName; NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表 if (null == sheet1) { sheet1 = book.CreateSheet(sheetName); } else if (sheet1.SheetName != sheetName) { book.SetSheetName(sheetIndex, sheetName); } //2.0给sheet1添加第一行的头部标题 if (!isCustomHead || tbColumnNames == null || tbColumnNames.Count != dt.Columns.Count) { tbColumnNames = new List <string>(); foreach (DataColumn item in dt.Columns) { tbColumnNames.Add(item.ColumnName); } } NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行 for (int i = 0; i < tbColumnNames.Count; i++) { rowHead.CreateCell(i).SetCellValue(tbColumnNames[i]); } //3.0 填充表格数据 for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1); //创建数据行 for (int j = 0; j < dt.Columns.Count; j++) //填充行数据 { rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } //4.0 写入文件 using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create)) { book.Write(wfile); } } catch (Exception ex) { if (errorFun != null) { errorFun(ex); } return(false); } return(true); }
private void sb_import_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); DataTable dt = null; OpenFileDialog sflg = new OpenFileDialog(); sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { string st_name = book.GetSheetName(sheetIndex); XtraTabPage xinka = new XtraTabPage(); xinka.Name = "xin"; xinka.Text = st_name; NepCalaTable xintab = new NepCalaTable(); xintab.Dock = DockStyle.Fill; xinka.Controls.Add(xintab); this.xtraTabControl1.TabPages.Add(xinka); this.xtraTabControl1.SelectedTabPage = xinka; this.active_nepCalaTable = xintab; NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new DataTable(sheet.SheetName); dt.Columns.Add("bushe_xianshu", typeof(int)); //MessageBox.Show(dt.Columns["bushe_xianshu"].DataType.ToString()); dt.Columns.Add("bushe_daoshu", typeof(int)); dt.Columns.Add("bushe_zongdaoshu", typeof(int)); dt.Columns.Add("banqian_daoshu", typeof(int)); dt.Columns.Add("ke_caiji", typeof(int)); dt.Columns.Add("banjia_daoshu", typeof(int)); dt.Columns.Add("hengxiangchang", typeof(int)); dt.Columns.Add("zongxiangchang", typeof(int)); dt.Columns.Add("zonghengbi", typeof(double)); dt.Columns.Add("paodaobi", typeof(double)); lastCellNum = 10; for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = 1; i <= sheet.LastRowNum; i++) { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } NPOI.SS.UserModel.IRow row0 = sheet.GetRow(0); this.active_nepCalaTable.jifa_dianju.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(1); this.active_nepCalaTable.jieshou_dianju.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(2); this.active_nepCalaTable.jieshou_dianshu.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(3); this.active_nepCalaTable.jifa_xianju.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(4); this.active_nepCalaTable.jieshou_xianju.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(5); this.active_nepCalaTable.muban_zong.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(6); this.active_nepCalaTable.mobanpao.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(7); this.active_nepCalaTable.jieshou_xianshu.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(8); this.active_nepCalaTable.muban_heng.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(9); this.active_nepCalaTable.bushe_jieshouxianshu.Text = row0.GetCell(13).StringCellValue; row0 = sheet.GetRow(10); this.active_nepCalaTable.bushe_jifaxianshu.Text = row0.GetCell(13).StringCellValue; ds.Tables.Add(dt); this.active_nepCalaTable.DDT = dt; } }
public string UpLoadFile() { try { var Filename = PostObjectDateValue("Filename"); //var File = PostObjectValue("File"); DateTime dateTime = Convert.ToDateTime(Filename); string year = dateTime.Year.ToString(); string month = dateTime.Month.ToString(); string day = dateTime.Day.ToString(); string filename = year + "-" + month + "-" + day; string tagpath = @"C:\Users\HK\Desktop\" + filename + @"(Mapping)\"; string listfilepath = @"C:\Users\HK\Desktop\" + filename + @"(Mapping)\List.xls"; List <MailInfo> MailInfoList = new List <MailInfo>(); List <string> FailedList = new List <string>(); // DataSet ds = new DataSet(); //DataTable dt = null; NPOI.HSSF.UserModel.HSSFWorkbook book; try { FileStream fs = new FileStream(listfilepath, FileMode.Open, FileAccess.Read); book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); } catch (Exception e) { return(GetJsonString("Error", "打开文件错误!" + e)); } DateTime now = DateTime.Now; int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = 7; if (firstCellNum == lastCellNum) { continue; } //dt = new DataTable(sheet.SheetName); //for (int i = firstCellNum; i < lastCellNum; i++) //{ // dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); //} try { for (int i = 0; i <= sheet.LastRowNum; i++) { //DataRow newRow = dt.Rows.Add(); MailInfo mailInfo = new MailInfo(); mailInfo.F_UploadDate = now.Date; mailInfo.F_IshaveFile = 0; for (int j = firstCellNum; j < lastCellNum; j++) { //newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; if (j == firstCellNum) { if (sheet.GetRow(i).GetCell(j) != null) { mailInfo.CompanyName = (sheet.GetRow(i).GetCell(j).ToString().Trim()); } else { mailInfo.CompanyName = ""; } if (mailInfo.CompanyName[mailInfo.CompanyName.Length - 1] == '.') { mailInfo.CompanyName = mailInfo.CompanyName.Substring(0, mailInfo.CompanyName.Length - 1); } } else if (j == firstCellNum + 1) { if (sheet.GetRow(i).GetCell(j) != null) { mailInfo.F_FaildReason = sheet.GetRow(i).GetCell(j).ToString(); } else { mailInfo.F_FaildReason = ""; } } else if (j == firstCellNum + 2) { if (sheet.GetRow(i).GetCell(j) != null) { mailInfo.F_ClassName = sheet.GetRow(i).GetCell(j).ToString().Replace(".", ""); } else { mailInfo.F_ClassName = ""; } } else if (j == firstCellNum + 3) { if (sheet.GetRow(i).GetCell(j) != null) { mailInfo.F_DealType = sheet.GetRow(i).GetCell(j).ToString(); } else { mailInfo.F_DealType = ""; } } else if (j == firstCellNum + 4) { if (sheet.GetRow(i).GetCell(j) != null) { mailInfo.F_DealResult = sheet.GetRow(i).GetCell(j).ToString(); } else { mailInfo.F_DealResult = ""; } } else if (j == firstCellNum + 5) { if (sheet.GetRow(i).GetCell(j) != null) { mailInfo.F_FileEndingDate = sheet.GetRow(i).GetCell(j).ToString().ToDateTime().Date; } else { mailInfo.F_FileEndingDate = DateTime.MinValue; } } else if (j == firstCellNum + 6) { if (sheet.GetRow(i).GetCell(j) != null) { mailInfo.F_ChangeDetails = sheet.GetRow(i).GetCell(j).ToString(); } else { mailInfo.F_ChangeDetails = ""; } } } if (mailInfo.F_ClassName.Length > 3) { if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("XLS") != -1) { mailInfo.F_FileType = 1; } else if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("PDF") != -1) { mailInfo.F_FileType = 2; } else if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("XML") != -1) { mailInfo.F_FileType = 3; } else if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("CVS") != -1) { mailInfo.F_FileType = 4; } else { mailInfo.F_FileType = 0; } } if (mailInfo.F_DealResult.ToUpper().IndexOf("PENDING") != -1) { mailInfo.F_FileType = -1; mailInfo.F_ClassName = ""; } MailInfoList.Add(mailInfo); } } catch { } //ds.Tables.Add(dt); } //SqlConnection conn = new SqlConnection(SqlHelper.MyCONNECTSTRING); if (MailInfoList.Count > 0) { try { string filepath = tagpath; filepath = tagpath + "Debug"; List <FilePath> DebugFilelist = new List <FilePath>(); string[] diarrdebug = Directory.GetDirectories(@filepath, "*", SearchOption.AllDirectories); for (int i = 0; i < diarrdebug.Length; i++) { string[] rootfilelist = System.IO.Directory.GetFiles(diarrdebug[i]); List <File> filelist = new List <File>(); for (int j = 0; j < rootfilelist.Length; j++) { if (rootfilelist[j].Contains("rar") || rootfilelist[j].ToUpper().IndexOf("MAIL.TXT") != -1) { continue; } else { File file = new File(); file.Path = rootfilelist[j]; file.Type = rootfilelist[j].Substring(rootfilelist[j].LastIndexOf(".") + 1).ToUpper(); file.isbeenread = false; filelist.Add(file); } } FilePath filePath = new FilePath(); filePath.Path = diarrdebug[i]; filePath.ChildPath = filelist; DebugFilelist.Add(filePath); } filepath = tagpath + "New"; List <FilePath> NewFilelist = new List <FilePath>(); string[] diarrnew = Directory.GetDirectories(@filepath, "*", SearchOption.AllDirectories); for (int i = 0; i < diarrnew.Length; i++) { string[] rootfilelist = Directory.GetFiles(diarrnew[i]); List <File> filelist = new List <File>(); for (int j = 0; j < rootfilelist.Length; j++) { if (rootfilelist[j].Contains("rar") || rootfilelist[j].ToUpper().IndexOf("MAIL.TXT") != -1) { continue; } else { File file = new File(); file.Path = rootfilelist[j]; file.Type = rootfilelist[j].Substring(rootfilelist[j].LastIndexOf(".") + 1).ToUpper(); file.isbeenread = false; filelist.Add(file); } } FilePath filePath = new FilePath(); filePath.Path = diarrnew[i]; filePath.ChildPath = filelist; NewFilelist.Add(filePath); } filepath = tagpath + "Renew"; List <FilePath> RenewFilelist = new List <FilePath>(); string[] diarrrenew = Directory.GetDirectories(@filepath, "*", SearchOption.AllDirectories); for (int i = 0; i < diarrrenew.Length; i++) { string[] rootfilelist = System.IO.Directory.GetFiles(diarrrenew[i]); List <File> filelist = new List <File>(); for (int j = 0; j < rootfilelist.Length; j++) { if (rootfilelist[j].Contains("rar") || rootfilelist[j].ToUpper().IndexOf("MAIL.TXT") != -1) { continue; } else { File file = new File(); file.Path = rootfilelist[j]; file.Type = rootfilelist[j].Substring(rootfilelist[j].LastIndexOf(".") + 1).ToUpper(); file.isbeenread = false; filelist.Add(file); } } FilePath filePath = new FilePath(); filePath.Path = diarrrenew[i]; filePath.ChildPath = filelist; RenewFilelist.Add(filePath); } foreach (var item in MailInfoList) { item.CompanyID = GetCompanyIdbyNameDB(item.CompanyName); item.F_LastModifiedTime = DateTime.Now; item.F_LastModifiedUser = "******"; long InsertId = InserttotableDB(item); if (InsertId > 0) { //插入到另一个数据表中 MailData mailData = new MailData { EmailFID = InsertId, F_UploadDate = item.F_UploadDate, F_LastModifiedDate = DateTime.Now, }; if (item.F_DealType.ToUpper().IndexOf("DEBUG") != -1) { var fileTypeAndData = GetFileDataAndType(item, DebugFilelist); mailData.F_FileData = fileTypeAndData.Data; mailData.F_FileType = fileTypeAndData.Type; } else if (item.F_DealType.ToUpper() == "NEW") { var fileTypeAndData = GetFileDataAndType(item, NewFilelist); mailData.F_FileData = fileTypeAndData.Data; mailData.F_FileType = fileTypeAndData.Type; } else if (item.F_DealType.ToUpper() == "RENEW") { var fileTypeAndData = GetFileDataAndType(item, RenewFilelist); mailData.F_FileData = fileTypeAndData.Data; mailData.F_FileType = fileTypeAndData.Type; } mailData.F_LastModifiedUser = "******"; if (mailData.F_FileData != null) { long FID = DataInsertToTableDB(mailData); if (FID > 0) { UpdateishavefileDB(InsertId, FID); } } else { FailedList.Add(item.CompanyName + "未找到文件,请检查"); } } } //===========================================BulkInsert======================================================== //SqlBulkCopy SqlBulkCopy =new SqlBulkCopy( SqlHelper.MyCONNECTSTRING); //SqlBulkCopy.DestinationTableName = "IridianDev2.dbo.OnlieMapping"; //DataTable dt = ToDataTable(MailInfoList); //for (int i = 0; i < dt.Columns.Count; i++) //{ // SqlBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); //} //SqlBulkCopy.WriteToServer(dt); //===========================================BulkInsert======================================================== } catch (Exception ex) { return(GetJsonString("Error", ex.Message)); } finally { // conn.Dispose(); } return(GetJsonString("Result", MailInfoList.Count, "Info", FailedList)); } return(GetJsonString("Error", "本次提交的文件中没有数据.")); } catch (Exception ex) { return(GetJsonString("Error", ex.Message)); } }
/// <summary> /// Excel转table 导入 Michaux 20160531 /// </summary> /// <param name="file"></param> /// <returns></returns> public DataTable ImportExcelToDataTable(HttpPostedFileBase file,Dictionary<string,ExcelFormatter> formatColumn=null) { var datatable = new DataTable(); if (file.FileName.IndexOf("xlsx") > -1) { NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { //if (formatColumn!=null && formatColumn.ContainsKey(firstRow.GetCell(j).StringCellValue)) //{ //} var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } else { NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } #region 清除最后的空行 for (int i = datatable.Rows.Count - 1; i > 0; i--) { bool isnull = true; for (int j = 0; j < datatable.Columns.Count; j++) { if (datatable.Rows[i][j] != null) { if (datatable.Rows[i][j].ToString() != "") { isnull = false; break; } } } if (isnull) { datatable.Rows[i].Delete(); } } #endregion return datatable; }
private static DataTable Convert(string filePath) { using (FileStream stream = new FileStream(ServerHelper.MapPath(filePath), FileMode.Open)) { string sheetName = "Sheet1"; DataTable data = new DataTable(); ISheet sheet = null; int startRow = 0; try { IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { dataRow[j] = row.GetCell(j).ToString(); } } data.Rows.Add(dataRow); } } return(data); } catch (Exception ex) { return(null); } } }
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword) { ClearTempTable(); NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Filename, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename); NPOI.HSSF.UserModel.HSSFSheet workSheet = null; //foreach (ExcelLibrary.SpreadSheet.Worksheet tmpWorkSheet in workBook.Worksheets) //{ // if (tmpWorkSheet.Name.Trim().Equals("RosterTable")) // { // workSheet = tmpWorkSheet; // break; // } //} try { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheet("RosterTable"); if (workSheet == null) { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); } } catch { if (workSheet == null) { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); } } if (workSheet.GetRow(ROW_YEAR).GetCell(0).StringCellValue.Trim().StartsWith("Year", StringComparison.CurrentCultureIgnoreCase)) { if (workSheet.GetRow(ROW_YEAR).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { m_importYear = Convert.ToInt32(workSheet.GetRow(ROW_YEAR).GetCell(1).NumericCellValue); } else { errors.addError("Invalid Year", null); } } if (workSheet.GetRow(ROW_MONTH).GetCell(0).StringCellValue.Trim().StartsWith("Month", StringComparison.CurrentCultureIgnoreCase)) { if (workSheet.GetRow(ROW_MONTH).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { m_importMonth = Convert.ToInt32(workSheet.GetRow(ROW_MONTH).GetCell(1).NumericCellValue); } else { errors.addError("Invalid Month", null); } //if (!int.TryParse(workSheet.GetRow(1).GetCell(1).StringCellValue.Trim(), out m_importMonth)) // errors.addError("Invalid Month", null); } if (errors.List.Count > 0) { throw (new HRImportException(errors.Message())); } int intHeaterRow = ROW_CALENDAR_HEADER; int intEmpColumn = 0; ArrayList results = new ArrayList(); int rowCount = 1; NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow); while (intEmpColumn <= headerRow.LastCellNum) { if (headerRow.GetCell(intEmpColumn) != null) { if (headerRow.GetCell(intEmpColumn).StringCellValue.Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase)) { break; } } intEmpColumn++; } if (intEmpColumn > headerRow.LastCellNum) { // do exception } int intEmptyEmpNoCount = 0; try { while (intHeaterRow + rowCount <= workSheet.LastRowNum) { int EmpID = 0; string EmpNo = string.Empty; int colCount = 0; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow + rowCount); if (row == null) { rowCount++; intEmptyEmpNoCount++; continue; } if (row.GetCell(intEmpColumn) == null) { rowCount++; intEmptyEmpNoCount++; continue; } EmpNo = row.GetCell(intEmpColumn).ToString().Trim(); if (string.IsNullOrEmpty(EmpNo)) { rowCount++; intEmptyEmpNoCount++; continue; } intEmptyEmpNoCount = 0; EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID); if (EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else if (EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } colCount = intEmptyEmpNoCount + 1; int lastImportDay = 0; int intCurrentMonth = m_importMonth; int intCurrentYear = m_importYear; while (colCount < headerRow.LastCellNum) { if (headerRow.GetCell(colCount) == null) { if (lastImportDay <= 0) { colCount++; continue; } else { break; } } if (headerRow.GetCell(colCount).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { int day = Convert.ToInt32(headerRow.GetCell(colCount).NumericCellValue); if (day > 0 && day <= DateTime.DaysInMonth(intCurrentYear, intCurrentMonth)) { if (lastImportDay > day) { intCurrentMonth++; if (intCurrentMonth > 12) { intCurrentMonth = 1; intCurrentYear++; } } lastImportDay = day; string RosterCode; if (row.GetCell(colCount) != null) { RosterCode = row.GetCell(colCount).ToString().Trim(); } else { RosterCode = string.Empty; } EUploadRosterTable uploadRosterTable = new EUploadRosterTable(); uploadRosterTable.EmpID = EmpID; uploadRosterTable.RosterTableDate = new DateTime(intCurrentYear, intCurrentMonth, day); DateTime inTime, outTime; uploadRosterTable.RosterCodeID = Import.Parse.GetRosterCodeID(dbConn, RosterCode, out inTime, out outTime); if (!inTime.Ticks.Equals(0)) { uploadRosterTable.RosterTableOverrideInTime = inTime; } if (!outTime.Ticks.Equals(0)) { uploadRosterTable.RosterTableOverrideOutTime = outTime; } uploadRosterTable.SessionID = m_SessionID; uploadRosterTable.TransactionDate = UploadDateTime; uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode)) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() }); } else { EUploadRosterTable.db.insert(dbConn, uploadRosterTable); } } } colCount++; } rowCount++; } } catch (Exception e) { errors.addError(e.Message, null); } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(errors.Message())); } return(GetImportDataFromTempDatabase(null)); //org.in2bits.MyXls.XlsDocument xlsDoc = new org.in2bits.MyXls.XlsDocument(Filename); //org.in2bits.MyXls.Worksheet workSheet; //try //{ // workSheet = xlsDoc.Workbook.Worksheets["RosterTable"]; //} //catch //{ // workSheet = xlsDoc.Workbook.Worksheets[0]; //} //if (workSheet.Rows[1].GetCell(1).Value.ToString().Trim().Equals("Year", StringComparison.CurrentCultureIgnoreCase)) //{ // if (!int.TryParse(workSheet.Rows[1].CellAtCol(2).Value.ToString().Trim(), out m_importYear)) // errors.addError("Invalid Year", null); //} //if (workSheet.Rows[2].GetCell(1).Value.ToString().Trim().Equals("Month", StringComparison.CurrentCultureIgnoreCase)) //{ // if (!int.TryParse(workSheet.Rows[2].CellAtCol(2).Value.ToString().Trim(), out m_importMonth)) // errors.addError("Invalid Month", null); //} //if (errors.List.Count > 0) //{ // throw (new HRImportException(errors.Message())); //} //ushort intHeaderRow = 3; //ArrayList results = new ArrayList(); //ushort rowCount = 1; //try //{ // while (intHeaderRow + rowCount <= workSheet.Rows.MaxRow) // { // int EmpID = 0; // string EmpNo = string.Empty; // ushort colCount = 1; // org.in2bits.MyXls.Row row = workSheet.Rows[(ushort)(intHeaderRow + rowCount)]; // while (colCount <= row.MaxCellCol) // { // if (workSheet.Rows[intHeaderRow].CellAtCol(colCount).Value.ToString().Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase)) // { // EmpNo = row.CellAtCol(colCount).Value.ToString().Trim(); // EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID); // if (EmpID <= 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); // break; // } // colCount++; // } // colCount = 1; // while (colCount <= workSheet.Rows[(ushort)(intHeaderRow + rowCount)].MaxCellCol) // { // if (workSheet.Cells .Rows[(ushort)(intHeaderRow)].CellExists(colCount)) // { // string RosterCode = string.Empty; // if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellExists(colCount)) // if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellAtCol(colCount).Value != null) // RosterCode = row.CellAtCol(colCount).Value.ToString().Trim(); // int day = 0; // if (int.TryParse(workSheet.Rows[intHeaderRow].GetCell(colCount).Value.ToString().Trim(), out day)) // { // if (day > 0 && day <= DateTime.DaysInMonth(m_importYear, m_importMonth)) // { // EUploadRosterTable uploadRosterTable = new EUploadRosterTable(); // uploadRosterTable.EmpID = EmpID; // uploadRosterTable.RosterTableDate = new DateTime(m_importYear, m_importMonth, day); // uploadRosterTable.RosterCodeID = Import.Parse.*ID(dbConn, RosterCode); // uploadRosterTable.SessionID = m_SessionID; // uploadRosterTable.TransactionDate = UploadDateTime; // uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; // if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode)) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() }); // } // else // EUploadRosterTable.db.insert(dbConn, uploadRosterTable); // } // } // } // colCount++; // } // rowCount++; // } //} //catch (Exception e) //{ // errors.addError(e.Message, null); //} //if (errors.List.Count > 0) //{ // ClearTempTable(); // throw (new HRImportException(errors.Message())); //} //return GetImportDataFromTempDatabase(null); }
protected void btnGenerate_Click(object sender, EventArgs e) { const string FIELD_COMPANY = "Company"; const string FIELD_POSITION = "Position"; const string FIELD_PAYROLLGROUP = "Payroll Group"; const string FIELD_EMPNO = "EmployeeID"; const string FIELD_EMPENGFULLNAME = "English Name"; const string FIELD_CHINESENAME = "¤¤¤å©m¦W"; const string FIELD_HKID = @"HKID/Passport"; const string FIELD_PERIODFROM = "From"; const string FIELD_PERIODTO = "To"; const string FIELD_WAGESWORK = "Wages Paid"; const string FIELD_WORKHOURTOTAL = "Total Working Hours"; const string FIELD_RESTDAYTOTAL = "No. of Rest Day"; const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH"; const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave"; const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave"; ArrayList list = new ArrayList(); foreach (RepeaterItem i in Repeater.Items) { CheckBox cb = (CheckBox)i.FindControl("ItemSelect"); if (cb.Checked) { EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject(); WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb); list.Add(o); } } ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList(); if (list.Count > 0 && payPeriodList.Count > 0) { //const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] "; string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; //System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true); HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName); DataSet dataSet = new DataSet(); //export.GetDataSet(); DataTable dataTable = new DataTable("Payroll$"); dataSet.Tables.Add(dataTable); dataTable.Columns.Add(FIELD_COMPANY, typeof(string)); DBFilter hierarchyLevelFilter = new DBFilter(); Hashtable hierarchyLevelHashTable = new Hashtable(); hierarchyLevelFilter.add("HLevelSeqNo", true); ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter); foreach (EHierarchyLevel hlevel in hierarchyLevelList) { dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string)); hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel); } dataTable.Columns.Add(FIELD_POSITION, typeof(string)); dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string)); dataTable.Columns.Add(FIELD_EMPNO, typeof(string)); dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string)); dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string)); dataTable.Columns.Add(FIELD_HKID, typeof(string)); dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime)); dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime)); dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double)); dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double)); dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double)); int firstSummaryColumnPos = dataTable.Columns.Count; int firstDetailColumnPos = dataTable.Columns.Count; foreach (EPayrollPeriod payPeriod in payPeriodList) { if (EPayrollPeriod.db.select(dbConn, payPeriod)) { EPayrollGroup payrollGroup = new EPayrollGroup(); payrollGroup.PayGroupID = payPeriod.PayGroupID; EPayrollGroup.db.select(dbConn, payrollGroup); foreach (EEmpPersonalInfo empInfo in list) { EEmpPersonalInfo.db.select(dbConn, empInfo); EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID)); empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID)); if (PayrollStatus.SelectedValue.Equals("T")) { empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T")); } else { empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T")); } DataRow row = dataTable.NewRow(); row[FIELD_EMPNO] = empInfo.EmpNo; row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName; row[FIELD_CHINESENAME] = empInfo.EmpChiFullName; row[FIELD_HKID] = empInfo.EmpHKID; row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr; row[FIELD_PERIODTO] = payPeriod.PayPeriodTo; DBFilter empPosFilter = new DBFilter(); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID); if (empPos != null) { ECompany company = new ECompany(); company.CompanyID = empPos.CompanyID; if (ECompany.db.select(dbConn, company)) { row[FIELD_COMPANY] = company.CompanyCode; } EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { row[FIELD_POSITION] = position.PositionDesc; } DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); foreach (EEmpHierarchy empHierarchy in empHierarchyList) { EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID]; if (hierarchyLevel != null) { EHierarchyElement hierarchyElement = new EHierarchyElement(); hierarchyElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hierarchyElement)) { row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc; } } } EPayrollGroup curentPayGroup = new EPayrollGroup(); curentPayGroup.PayGroupID = empPos.PayGroupID; if (EPayrollGroup.db.select(dbConn, curentPayGroup)) { row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc; } } double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0; double mcER = 0, mcEE = 0; double vcER = 0, vcEE = 0; double pFundER = 0, pFundEE = 0; double wagesByWork = 0; double wagesByRest = 0; double fullPaidLeaveDays = 0; double nonFullPaidLeaveDays = 0; DBFilter paymentRecordFilter = new DBFilter(); paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); paymentRecordFilter.add(new Match("PayRecStatus", "A")); ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter); foreach (EPaymentRecord paymentRecord in paymentRecords) { EPaymentCode payCode = new EPaymentCode(); payCode.PaymentCodeID = paymentRecord.PaymentCodeID; EPaymentCode.db.select(dbConn, payCode); // Always Use Payment Code Description for grouping payment code with same description //string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc; //if (dataTable.Columns[fieldName] == null) // dataTable.Columns.Add(new DataColumn(fieldName, typeof(double))); //if (row[fieldName] == null || row[fieldName] == DBNull.Value) // row[fieldName] = 0; //row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount; netAmount += paymentRecord.PayRecActAmount; if (payCode.PaymentCodeIsMPF) { releventIncome += paymentRecord.PayRecActAmount; } else { nonRelevantIncome += paymentRecord.PayRecActAmount; } DBFilter taxPaymentMapFilter = new DBFilter(); taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID)); if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0) { taxableAmount += paymentRecord.PayRecActAmount; } else { nonTaxableAmount += paymentRecord.PayRecActAmount; } if (payCode.PaymentCodeIsWages) { if (paymentRecord.PayRecIsRestDayPayment) { wagesByRest += paymentRecord.PayRecActAmount; } else { wagesByWork += paymentRecord.PayRecActAmount; } } } DBFilter mpfRecordFilter = new DBFilter(); mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter); foreach (EMPFRecord mpfRecord in mpfRecords) { vcER += mpfRecord.MPFRecActVCER; mcER += +mpfRecord.MPFRecActMCER; vcEE += mpfRecord.MPFRecActVCEE; mcEE += mpfRecord.MPFRecActMCEE; } ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter); foreach (EORSORecord orsoRecord in orsoRecords) { pFundER += orsoRecord.ORSORecActER; pFundEE += orsoRecord.ORSORecActEE; } row[FIELD_WAGESWORK] = wagesByWork; DBFilter workingSummaryFilter = new DBFilter(); workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo)); } workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID)); ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter); double workHourTotal = 0, restDayTotal = 0; foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList) { workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours; restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled; } row[FIELD_WORKHOURTOTAL] = workHourTotal; row[FIELD_RESTDAYTOTAL] = restDayTotal; DBFilter statutoryHolidayFilter = new DBFilter(); statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo)); } ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter); double restDayCount = 0; foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList) { restDayCount++; } row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount; DBFilter LeaveAppEmpPayrollFilter = new DBFilter(); LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter); foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists) { ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = leaveApp.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { if (leaveCode.LeaveCodePayRatio >= 1) { fullPaidLeaveDays += leaveApp.LeaveAppDays; } else { nonFullPaidLeaveDays += leaveApp.LeaveAppDays; } } } row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays; row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays; dataTable.Rows.Add(row); } } } //DBFilter paymentCodeFilter = new DBFilter(); //paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false); //paymentCodeFilter.add("PaymentCode", false); //ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter); //foreach (EPaymentCode paymentCode in paymentCodeList) //{ // if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc)) // { // DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc]; // paymentColumn.SetOrdinal(firstDetailColumnPos); // if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc)) // paymentColumn.ColumnName = paymentCode.PaymentCodeDesc; // else // { // Console.Write("System reserved payment column is used"); // } // } //} //for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++) // dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1); export.Update(dataSet); System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1); NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0); if (excelRow == null) { excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0); } NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0); if (excelCell == null) { excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0); } excelCell.SetCellValue("Statutory Minimum Wage Summary Report"); excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); workbook.Write(excelfileStream); excelfileStream.Close(); WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError("Employee not selected"); } }
public static void ImportExcel2DGV(DataGridView dgv) { OpenFileDialog ofd = new OpenFileDialog(); ofd.Title = "选择待导入的数据文件"; ofd.Filter = "Excel Workbook 97-2003|*.xls|Excel Workbook|*.xlsx"; string filePath; if (ofd.ShowDialog() == DialogResult.OK) { filePath = Path.GetFullPath(ofd.FileName); FileStream fs; try { fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; for (int index = 0; index < sheetCount; index++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(index); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum || lastCellNum - firstCellNum < 2) { continue; } //MainForm.dataBindings.Clear(); //清空表中数据 //确定第一行是否是数据行 int count = 0; //记录表中数据字段的总数 int start_row = 0; for (int i = firstCellNum; i < lastCellNum; i++) { //if(row.GetCell(i) != null) //{ //row.GetCell(i).SetCellType(NPOI.SS.UserModel.CellType.String); if (GPA_CALC.isNum(row.GetCell(i).StringCellValue)) { count++; } //} } if (count == 0) { start_row = 1; } for (int i = start_row; i <= sheet.LastRowNum; i++) { string courseName = sheet.GetRow(i).Cells[0].StringCellValue; //MessageBox.Show(sheet.GetRow(i).Cells.Count.ToString()); double credit; double grade; try { sheet.GetRow(i).Cells[1].SetCellType(NPOI.SS.UserModel.CellType.String); sheet.GetRow(i).Cells[2].SetCellType(NPOI.SS.UserModel.CellType.String); credit = Convert.ToDouble(sheet.GetRow(i).Cells[1].StringCellValue); grade = Convert.ToDouble(sheet.GetRow(i).Cells[2].StringCellValue); } catch (Exception e) { MessageBox.Show(e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); credit = 0; grade = 0; } //MessageBox.Show(sheet.LastRowNum.ToString()); InfoVo info = new InfoVo() { CourseName = courseName, Credit = credit, Grade = grade }; //原创的处理InvalidOperationException的方法,当选中新行时会触发使得dataBinding添加异常的一行 if (dgv.CurrentRow != null && dgv.CurrentRow.IsNewRow)//别缺少前面的条件了,否则NRE { MainForm.dataBindings.RemoveAt(MainForm.dataBindings.Count - 1); } //dgv.ClearSelection(); MainForm.dataBindings.Add(info); } } fs.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
private void button13_Click(object sender, EventArgs e) { //清空报表 report1.ClearReport(); //设置报表为非只读 report1.ReadOnly = false; //报表停止公式计算,这样速度快点 report1.CalcFormula(false); //报表停止绘制 report1.AllowDraw(false); string fileName = Application.StartupPath + "\\Test.xls"; NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook; try { this.Cursor = Cursors.WaitCursor; using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream); NPOI.HSSF.UserModel.HSSFSheet sheet = hssfworkbook.GetSheetAt(0); if (sheet.LastRowNum == 0) { this.Cursor = Cursors.Arrow; return; } else { //产生列 NPOI.HSSF.UserModel.HSSFRow row = sheet.GetRow(0); //report1.AddColumn(row.Cells.Count); report1.AddColumn(row.LastCellNum); } int rowIndex = 0; System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { //增加行 rowIndex = report1.AddRow(Gscr.Band.Detail); NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current; for (int i = 0; i < row.LastCellNum; i++) { Gscr.Cell cellRpt = report1[rowIndex, i]; NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(i); if (cell == null) { cellRpt.Value = null; } else { switch (cell.CellType) { case NPOI.HSSF.UserModel.HSSFCellType.BLANK: cellRpt.Value = null; break; case NPOI.HSSF.UserModel.HSSFCellType.BOOLEAN: cellRpt.Value = cell.BooleanCellValue; break; case NPOI.HSSF.UserModel.HSSFCellType.NUMERIC: cellRpt.Value = cell.ToString(); break; case NPOI.HSSF.UserModel.HSSFCellType.STRING: cellRpt.Value = cell.StringCellValue; break; case NPOI.HSSF.UserModel.HSSFCellType.ERROR: cellRpt.Value = cell.ErrorCellValue; break; default: break; } } } } } //报表恢复公式计算 report1.CalcFormula(true); //报表恢复绘制 report1.AllowDraw(true); this.Cursor = Cursors.Arrow; MessageBox.Show("成功读取Excel文件【" + fileName + " 】。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception err) { report1.CalcFormula(true); report1.AllowDraw(true); this.Cursor = Cursors.Arrow; MessageBox.Show("读取Excel文件失败。" + err.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Stop); } }
/// <summary> /// 导出Excel /// </summary> /// <param name="ds">要导出的数据源</param> /// <param name="excelFilePath">文件的物理路径</param> /// <param name="errorFun"></param> /// <returns></returns> public static bool Output(DataSet ds, string excelFilePath, Action <Exception> errorFun = null) { if (!System.IO.File.Exists(excelFilePath)) { throw new Exception("Excel 文件不存在"); } if (null == ds || ds.Tables.Count == 0) { return(false); } try { //1.0 创建工作薄 和 工作表对象 NPOI.HSSF.UserModel.HSSFWorkbook book; using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite)) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile); } int sheetIndex = 0; foreach (DataTable dt in ds.Tables) { string sheetName = dt.TableName ?? "sheet" + sheetIndex; NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表 if (null == sheet1) { sheet1 = book.CreateSheet(sheetName); } else if (sheet1.SheetName != sheetName) { book.SetSheetName(sheetIndex, sheetName); } int beginRow = 1; //2.0给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行 for (int i = 0; i < dt.Columns.Count; i++) { rowHead.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } //3.0 填充表格数据 for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + beginRow); //创建数据行 for (int j = 0; j < dt.Columns.Count; j++) //填充行数据 { rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } sheetIndex++; } //4.0 写入文件 using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create)) { book.Write(wfile); } return(true); } catch (Exception ex) { if (errorFun != null) { errorFun(ex); } return(false); } }
public DataTable ImportExcelBase(HttpPostedFileBase file, out Dictionary <int, PicturesInfo> ImgList, Dictionary <string, ExcelFormatter> formatColumn = null) { var datatable = new DataTable(); ImgList = new Dictionary <int, PicturesInfo>(); if (file.FileName.IndexOf("xlsx") > -1) { NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } bool imgForm = true; for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { if (formatColumn != null && formatColumn.Values.Where(x => x.ColumnName == firstRow.GetCell(j).StringCellValue).Any()) { ExcelFormatter excelFormatter = formatColumn.Values.Where(x => x.ColumnName == firstRow.GetCell(j).StringCellValue).FirstOrDefault(); if (excelFormatter.ColumnTrans.Equals(EnumColumnTrans.ConvertImportImage)) { if (imgForm) { ImgList = NPOIExtendImg.GetAllPictureInfos(sheet); imgForm = false; } datarow[j] = GetImgsUrl(ImgList, i - 1); continue; } } var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } else { NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } #region 清除最后的空行 for (int i = datatable.Rows.Count - 1; i > 0; i--) { bool isnull = true; for (int j = 0; j < datatable.Columns.Count; j++) { if (datatable.Rows[i][j] != null) { if (datatable.Rows[i][j].ToString() != "") { isnull = false; break; } } } if (isnull) { datatable.Rows[i].Delete(); } } #endregion return(datatable); }