public async Task <ActionResult> ImportAsync() { IFormFile file = Request.Form.Files[0]; string folderName = "UploadExcel"; string webRootPath = hostEnvironment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); var errorDictionary = new Dictionary <int, string>(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, file.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } var newProduct = new ProductInputModel(); for (int j = row.FirstCellNum; j < cellCount; j++) { string currentRow = string.Empty; if (row.GetCell(j) != null) { currentRow = row.GetCell(j).ToString().TrimEnd(); } switch (j) { case 0: if (currentRow != "") { newProduct.Name = currentRow; } else { errorDictionary[i] = currentRow; } break; case 1: if (currentRow != "") { newProduct.ShortName = currentRow; } else { errorDictionary[i] = currentRow; } break; case 2: if (this.numbersChecker.WholeNumberCheck(currentRow)) { newProduct.BrandexId = int.Parse(currentRow); } else { errorDictionary[i] = currentRow; } break; case 3: if (currentRow != "") { newProduct.PhoenixId = int.Parse(currentRow); } break; case 4: if (currentRow != "") { newProduct.PharmnetId = int.Parse(currentRow); } break; case 5: if (currentRow != "") { newProduct.StingId = int.Parse(currentRow); } break; case 6: if (currentRow != "") { newProduct.SopharmaId = currentRow; } break; case 7: if (numbersChecker.NegativeNumberIncludedCheck(currentRow)) { newProduct.Price = double.Parse(currentRow); } else { errorDictionary[i] = currentRow; } break; } } await this.productsService.CreateProduct(newProduct); } } } var productsErrorModel = new CustomErrorDictionaryOutputModel { Errors = errorDictionary }; return(this.View(productsErrorModel)); }
public void TestGetFormattedCellValueHSSFCell() { // Valid date formats -- cell values should be date formatted & not "555.555" IRow row = wb.GetSheetAt(0).GetRow(0); IEnumerator it = row.GetEnumerator(); log("==== VALID DATE FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; String fmtval = formatter.FormatCellValue(cell); log(fmtval); // should not be equal to "555.555" Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); Assert.IsTrue(!"555.555".Equals(fmtval)); String fmt = cell.CellStyle.GetDataFormatString(); //assert the correct month form, as in the original Excel format String monthPtrn = fmt.IndexOf("mmmm") != -1 ? "MMMM" : "MMM"; // this line is intended to compute how "July" would look like in the current locale String jul = new SimpleDateFormat(monthPtrn).Format(new DateTime(2010, 7, 15), CultureInfo.CurrentCulture); // special case for MMMMM = 1st letter of month name if (fmt.IndexOf("mmmmm") > -1) { jul = jul.Substring(0, 1); } // check we found july properly Assert.IsTrue(fmtval.IndexOf(jul) > -1, "Format came out incorrect - " + fmt); } row = wb.GetSheetAt(0).GetRow(1); it = row.GetEnumerator(); log("==== VALID TIME FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; String fmt = cell.CellStyle.GetDataFormatString(); String fmtval = formatter.FormatCellValue(cell); log(fmtval); // should not be equal to "555.47431" Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); Assert.IsTrue(!"555.47431".Equals(fmtval)); // check we found the time properly Assert.IsTrue(fmtval.IndexOf("11:23") > -1, "Format came out incorrect - " + fmt); } // Test number formats row = wb.GetSheetAt(0).GetRow(1); it = row.GetEnumerator(); log("\n==== VALID NUMBER FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; log(formatter.FormatCellValue(cell)); // should not be equal to "1234567890.12345" Assert.IsTrue(!"1234567890.12345".Equals(formatter.FormatCellValue(cell))); } // Test bad number formats row = wb.GetSheetAt(0).GetRow(3); it = row.GetEnumerator(); log("\n==== INVALID NUMBER FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; log(formatter.FormatCellValue(cell)); // should be equal to "1234567890.12345" // in some locales the the decimal delimiter is a comma, not a dot string decimalSeparator = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator; Assert.AreEqual("1234567890" + decimalSeparator + "12345", formatter.FormatCellValue(cell)); } // Test Zip+4 format row = wb.GetSheetAt(0).GetRow(4); ICell cell1 = row.GetCell(0); log("\n==== ZIP FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("12345-6789", formatter.FormatCellValue(cell1)); // Test phone number format row = wb.GetSheetAt(0).GetRow(5); cell1 = row.GetCell(0); log("\n==== PHONE FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("(555) 123-4567", formatter.FormatCellValue(cell1)); // Test SSN format row = wb.GetSheetAt(0).GetRow(6); cell1 = row.GetCell(0); log("\n==== SSN FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("444-55-1234", formatter.FormatCellValue(cell1)); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); }
/// <summary> /// 读取2003及以前版本.xls /// </summary> /// <param name="path"></param> /// <returns></returns> public static DataTable Read2003ToTable(string path) { HSSFWorkbook hssfworkbook; path = HttpContext.Current.Server.MapPath(path); using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); /* * ②:将文档保存到指定路径 */ string destFileName = @"D:\test.xls"; //HSSFWorkbook hssfworkbook2 = writeToExcel(); MemoryStream msfile = new MemoryStream(); hssfworkbook.Write(msfile); System.IO.File.WriteAllBytes(destFileName, msfile.ToArray()); DataTable dt = new DataTable(); for (int j = 0; j < 5; j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } bool firstr = true; while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; #region 第一行,初始化dt if (firstr) { for (int j = 0; j < row.LastCellNum; j++) { dt.Columns.Add("column" + j); } firstr = false; } #endregion for (int i = 0; i < row.LastCellNum; i++) { HSSFCell cell = (HSSFCell)row.GetCell(i); DataRow dr = dt.NewRow(); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } dt.Rows.Add(dr); } } return(dt); }
/// <summary> /// /// </summary> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <param name="isFirstRowColumn"></param> /// <param name="columnTemplate"></param> /// <param name="requireColumns"></param> /// <param name="maxRows"></param> /// <returns></returns> public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn, Dictionary <string, string[]> columnTemplate = null, string[] requireColumns = null, int?maxRows = null) { ISheet sheet = null; DataTable data = new DataTable(); IWorkbook workbook = null; int startRow = 0; try { using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { try { workbook = new XSSFWorkbook(fs); } catch { workbook = new HSSFWorkbook(fs); } } if (sheetName != null) { if (workbook != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } } else { if (workbook != null) { sheet = workbook.GetSheetAt(0); } } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); string cellValue = cell?.StringCellValue?.Trim(); if (!string.IsNullOrWhiteSpace(cellValue))//列名正确性验证 { if (columnTemplate != null && !columnTemplate.First().Value.Contains(cellValue)) { throw new Exception($"{columnTemplate.First().Key}不存在列名:{cellValue}!正确列名为:{string.Join(",", columnTemplate.First().Value)}"); } DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; if (maxRows != null) { if (rowCount > maxRows) { throw new Exception($"请拆分文件,一次最多支持{maxRows}条数据"); } } for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null || row.Cells.Count == 0 || row.FirstCellNum == -1 || row.Cells.All(d => d.CellType == CellType.Blank)) { continue; //没有数据的行默认是null } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { var cellvalue = row.GetCell(j); if (cellvalue == null || (cellvalue.ToString().Trim() == "0")) { if (requireColumns != null && requireColumns.Contains(data.Columns[j].ColumnName)) { //throw new Exception($"第{i}行,第{j}列,【{data.Columns[j].ColumnName}】不能为空或0,必须填写!"); } } if (cellvalue != null) { dataRow[j] = cellvalue.ToString().Trim(); } else { dataRow[j] = ""; //string.Empty; } } data.Rows.Add(dataRow); } } workbook?.Close(); return(data); } catch (Exception ex) { workbook?.Close(); throw new Exception(ex.Message); } }
public string ExportExcel(StoreCondition input, string path) { if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } var sqlParamters = new List <SqlParameter>(); sqlParamters.Add(new SqlParameter("@name", input.Name ?? "")); sqlParamters.Add(new SqlParameter("@Start", input.Start ?? "")); sqlParamters.Add(new SqlParameter("@End", input.End ?? "")); var dt = Repository.ExecuteQuery <TakeCheckOut>("EXEC sp_GetExportTakeCheck @name,@Start,@End", sqlParamters.ToArray()); //创建EXCEL工作薄 HSSFWorkbook workBook = null; using (FileStream fs = File.Open(path + "/takelist.xls", FileMode.Open, System.IO.FileAccess.Read, FileShare.Read)) { workBook = new HSSFWorkbook(fs); fs.Close(); } //创建sheet文件表 Sheet sheet = workBook.GetSheetAt(0);//workBook..CreateSheet("提现列表"); int r = 0; Dictionary <string, object> dict = new Dictionary <string, object>(); //创建表头 if (dt.Count() > 0) { Expression <Func <TakeCheck> > properties = (() => new TakeCheck() { Id = 0, StoreName = "", TakeOut = 0, Account = "", Bank = "", BankNo = "", CreateTime = DateTime.Now, ExportCount = 0, ConfirmTime = DateTime.Now }); dict = properties.GetPropertyWithValue(); //Cell cell = header.CreateCell(r); // cell.SetCellValue("头部"); //foreach (var item in dict.Keys) //{ // string colunname = GetColumnName(item); // if (colunname != "") // { // Cell cell = header.CreateCell(r); // cell.SetCellValue(colunname); // r++; // } //} } else { Row header = sheet.CreateRow(2); Cell cell = header.CreateCell(0); cell.SetCellValue("数据为空"); } #region 设置字体 Font font = workBook.CreateFont(); //创建字体样式 font.Color = HSSFColor.RED.index; //设置字体颜色 CellStyle style = workBook.CreateCellStyle(); //创建单元格样式 style.SetFont(font); //设置单元格样式中的字体样式 #endregion Row row; Dictionary <string, string> di; //数据 for (var i = 0; i < dt.Count(); i++) { row = sheet.CreateRow(i + 1); r = 0; TakeCheckOut model = dt.ElementAt(i); di = ExpressionHelper.GetProperties <TakeCheckOut>(model); foreach (var item in dict.Keys) { var cell = row.CreateCell(r); object obj = di[item]; if (obj != null) { cell.SetCellValue(obj.ToString()); } else { cell.SetCellValue(string.Empty); } if (item == "ExportCount") { var v = 0; if (int.TryParse(obj.ToString(), out v) && v > 0) { cell.CellStyle = style;//为单元格设置显示样式 } } if (item == "ConfirmTime") { cell.CellStyle = style; } r++; } } //转为字节数组 var stream = new MemoryStream(); workBook.Write(stream); path += System.Guid.NewGuid().GetHashCode().ToString("x") + ".xls"; using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write)) { byte[] data = stream.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); data = null; } stream.Close(); stream.Dispose(); if (File.Exists(path)) { Update(dt); return(path); } else { return(string.Empty); } }
/// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="sheetName">要导入的excel的sheet的名称</param> /// <returns>导入数据行数(包含列名那一行)</returns> public int DataTableToExcel(DataTable dt, string sheetName, bool isColumnWritten, bool Append = true) { try { HSSFWorkbook Workbook = NPOIOpenExcel(FileName);//打开工作薄 ISheet sheet = null; if (sheetName != null) { sheet = Workbook.GetSheet(sheetName); } if (sheet == null) { sheet = Workbook.GetSheetAt(0); } if (sheet == null) { return(0); } int nStartRow = sheet.LastRowNum + 1; if (!Append) { for (int i = 0; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null) { sheet.RemoveRow(row); } } nStartRow = 1; } //是否写入第一行 if (isColumnWritten) { IRow FirstRow = sheet.CreateRow(0); int K = 0; foreach (var it in dt.Columns) { FirstRow.CreateCell(K++).SetCellValue(it.ToString()); } } //不加第一行 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; IRow row = sheet.CreateRow(nStartRow + i); for (int j = 0; j < dt.Columns.Count; j++) //写一行的数据 { row.CreateCell(j).SetCellValue(dr[j].ToString()); } } WriteToFile(Workbook, FileName); } catch (Exception ex) { throw new Exception($"将DataTable写入到Excel文件出错:{ex.Message}"); } return(1); }
protected Sheet GetSheet() { return(workbook.GetSheetAt(0)); }
/// <summary> /// Goes through the Workbook, optimising the fonts by /// removing duplicate ones. /// For now, only works on fonts used in HSSFCellStyle /// and HSSFRichTextString. Any other font uses /// (eg charts, pictures) may well end up broken! /// This can be a slow operation, especially if you have /// lots of cells, cell styles or rich text strings /// </summary> /// <param name="workbook">The workbook in which to optimise the fonts</param> public static void OptimiseFonts(HSSFWorkbook workbook) { // Where each font has ended up, and if we need to // delete the record for it. Start off with no change short[] newPos = new short[workbook.Workbook.NumberOfFontRecords + 1]; bool[] zapRecords = new bool[newPos.Length]; for (int i = 0; i < newPos.Length; i++) { newPos[i] = (short)i; zapRecords[i] = false; } // Get each font record, so we can do deletes // without Getting confused FontRecord[] frecs = new FontRecord[newPos.Length]; for (int i = 0; i < newPos.Length; i++) { // There is no 4! if (i == 4) { continue; } frecs[i] = workbook.Workbook.GetFontRecordAt(i); } // Loop over each font, seeing if it is the same // as an earlier one. If it is, point users of the // later duplicate copy to the earlier one, and // mark the later one as needing deleting // Note - don't change built in fonts (those before 5) for (int i = 5; i < newPos.Length; i++) { // Check this one for being a duplicate // of an earlier one int earlierDuplicate = -1; for (int j = 0; j < i && earlierDuplicate == -1; j++) { if (j == 4) { continue; } FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j); if (frCheck.SameProperties(frecs[i])) { earlierDuplicate = j; } } // If we got a duplicate, mark it as such if (earlierDuplicate != -1) { newPos[i] = (short)earlierDuplicate; zapRecords[i] = true; } } // Update the new positions based on // deletes that have occurred between // the start and them // Only need to worry about user fonts for (int i = 5; i < newPos.Length; i++) { // Find the number deleted to that // point, and adjust short preDeletePos = newPos[i]; short newPosition = preDeletePos; for (int j = 0; j < preDeletePos; j++) { if (zapRecords[j]) { newPosition--; } } // Update the new position newPos[i] = newPosition; } // Zap the un-needed user font records for (int i = 5; i < newPos.Length; i++) { if (zapRecords[i]) { workbook.Workbook.RemoveFontRecord( frecs[i] ); } } // Tell HSSFWorkbook that it needs to // re-start its HSSFFontCache workbook.ResetFontCache(); // Update the cell styles to point at the // new locations of the fonts for (int i = 0; i < workbook.Workbook.NumExFormats; i++) { ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i); xfr.FontIndex = ( newPos[xfr.FontIndex] ); } // Update the rich text strings to point at // the new locations of the fonts // Remember that one underlying unicode string // may be shared by multiple RichTextStrings! ArrayList doneUnicodeStrings = new ArrayList(); for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++) { NPOI.SS.UserModel.Sheet s = workbook.GetSheetAt(sheetNum); IEnumerator rIt = s.GetRowEnumerator(); while (rIt.MoveNext()) { HSSFRow row = (HSSFRow)rIt.Current; IEnumerator cIt = row.GetCellEnumerator(); while (cIt.MoveNext()) { Cell cell = (HSSFCell)cIt.Current; if (cell.CellType == NPOI.SS.UserModel.CellType.STRING) { HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue; UnicodeString u = rtr.RawUnicodeString; // Have we done this string already? if (!doneUnicodeStrings.Contains(u)) { // Update for each new position for (short i = 5; i < newPos.Length; i++) { if (i != newPos[i]) { u.SwapFontUse(i, newPos[i]); } } // Mark as done doneUnicodeStrings.Add(u); } } } } } }
private void ProcessSheet5(HSSFWorkbook workbook, int fromYear, int toYear) { ISheet sheet = workbook.GetSheetAt(5); var startDatePeriod = new DateTime(2016, 4, 1); var endDatePeriod = new DateTime(toYear, 3, DateTime.DaysInMonth(toYear, 3)); var query = from state in stateHistories where state.EffectiveDate >= startDatePeriod && state.EffectiveDate <= endDatePeriod select state; var countATL = query.Where(s => s.StateCode == MutationCodes.ATL).Count(); var countATIOT = query.Where(s => s.StateCode == MutationCodes.ATIOT).Count(); var countATP2 = query.Where(s => s.StateCode == MutationCodes.ATP2).Count(); sheet.GetRow(10).GetCell(2).SetCellValue(countATL); sheet.GetRow(11).GetCell(2).SetCellValue(countATIOT); sheet.GetRow(12).GetCell(2).SetCellValue(countATP2); var countAPA = members.Where(s => s.WhenFlagApa.HasValue).Count(); sheet.GetRow(17).GetCell(2).SetCellValue(countAPA); var countATIS = query.Where(s => s.StateCode == MutationCodes.ATIS).Count(); var countATPS = query.Where(s => s.StateCode == MutationCodes.ATPS).Count(); var countATP1 = query.Where(s => s.StateCode == MutationCodes.ATP1).Count(); sheet.GetRow(23).GetCell(2).SetCellValue(countATIS); sheet.GetRow(24).GetCell(2).SetCellValue(countATPS); sheet.GetRow(25).GetCell(2).SetCellValue(countATP1); var countExDKH123 = query.Where(s => new[] { MutationCodes.ExDKH1, MutationCodes.ExDKH2, MutationCodes.ExDKH3 }.Contains(s.StateCode)).Count(); var countExDKH4 = query.Where(s => s.StateCode == MutationCodes.ExDKH4).Count(); sheet.GetRow(31).GetCell(2).SetCellValue(countExDKH123); sheet.GetRow(32).GetCell(2).SetCellValue(countExDKH4); var countATD1 = query.Where(s => s.StateCode == MutationCodes.ATD1).Count(); var countATD2 = query.Where(s => s.StateCode == MutationCodes.ATD2).Count(); sheet.GetRow(38).GetCell(2).SetCellValue(countATD1); sheet.GetRow(39).GetCell(2).SetCellValue(countATD2); var countAKK1 = query.Where(s => s.StateCode == MutationCodes.AKK1).Count(); var countAKK2 = query.Where(s => s.StateCode == MutationCodes.AKK2).Count(); var countAKK3 = query.Where(s => s.StateCode == MutationCodes.AKK3).Count(); sheet.GetRow(47).GetCell(2).SetCellValue(countAKK1); sheet.GetRow(48).GetCell(2).SetCellValue(countAKK2); sheet.GetRow(49).GetCell(2).SetCellValue(countAKK3); var countDKH1 = query.Where(s => s.StateCode == MutationCodes.DKH1).Count(); var countDKH2 = query.Where(s => s.StateCode == MutationCodes.DKH2).Count(); var countDKH3 = query.Where(s => s.StateCode == MutationCodes.DKH3).Count(); var countDKH4 = query.Where(s => s.StateCode == MutationCodes.DKH4).Count(); sheet.GetRow(56).GetCell(2).SetCellValue(countDKH1); sheet.GetRow(57).GetCell(2).SetCellValue(countDKH2); sheet.GetRow(58).GetCell(2).SetCellValue(countDKH3); sheet.GetRow(59).GetCell(2).SetCellValue(countDKH4); var countAKM1 = query.Where(s => s.StateCode == MutationCodes.AKM1).Count(); var countAKM2 = query.Where(s => s.StateCode == MutationCodes.AKM2).Count(); sheet.GetRow(66).GetCell(2).SetCellValue(countAKM1); sheet.GetRow(67).GetCell(2).SetCellValue(countAKM2); }
private void ProcessSheet3(HSSFWorkbook workbook, int fromYear, int toYear) { fromYear = 2016; DateTime startDatePeriod = GetStartDatePeriod(fromYear); DateTime endDatePeriod = GetEndDatePeriod(toYear); ISheet sheet = workbook.GetSheetAt(3); int rowIndex = 0; sheet.GetRow(1).GetCell(17).SetCellValue(DateTime.Today); foreach (Member member in members) { IRow currentRow = sheet.GetRow(5 + rowIndex); // MemberNo currentRow.GetCell(1).SetCellValue(member.MemberNo); // Nama currentRow.GetCell(2).SetCellValue(member.Name); // Alamat currentRow.GetCell(3).SetCellValue(member.Address); // No. Telp currentRow.GetCell(6).SetCellValue(member.HomePhone); // No. HP currentRow.GetCell(7).SetCellValue(member.CellPhone1); // Wilayah if (member.RegionId.HasValue) { string regionCode = member.Region.Code; string wilayah = regionCode.Any(chr => chr == '-') ? member.Region.Code.Split('-')[0] : regionCode; int wilayahInt; if (Int32.TryParse(wilayah, out wilayahInt)) { currentRow.GetCell(8).SetCellValue(wilayahInt.ToRoman()); } } // Gender currentRow.GetCell(9).SetCellValue(member.Gender == "L" ? "P" : "W"); // Gol Darah if (!String.IsNullOrEmpty(member.BloodType?.Trim())) { string rhesus = String.IsNullOrEmpty(member.Rhesus) ? "+" : member.Rhesus; currentRow.GetCell(10).SetCellValue(String.Format("{0}{1}", member.BloodType, rhesus)); } // Status Anggota currentRow.GetCell(11).SetCellValue(member.ChrismationDate.HasValue ? "S" : "B"); // Umur tidak perlu dihitung karena sudah ada formulanya di Excel //if(!member.DeceasedDate.HasValue) // currentRow.GetCell(12).SetCellValue(member.BirthDate.HasValue ? (DateTime.Today.Year - member.BirthDate.Value.Year).ToString() : ""); // Pendidikan string pendidikan = member.EducationGrade?.Name; if (!String.IsNullOrEmpty(pendidikan)) { currentRow.GetCell(13).SetCellValue(pendidikan); } // Pekerjaan int? jobId = member.JobId; string pekerjaan = String.Empty; if (jobId.HasValue) { Job job = context.Jobs.SingleOrDefault(j => j.Id == jobId.Value); if (job != null) { pekerjaan = job.Name ?? String.Empty; } currentRow.GetCell(14).SetCellValue(pekerjaan); } // Kelompok Etnis string etnis = GetKlasisMappingValue(member.EthnicId.GetValueOrDefault(), DBAJMappingEnum.Ethnic); if (!String.IsNullOrEmpty(etnis)) { currentRow.GetCell(15).SetCellValue(etnis); } // Lahir if (member.BirthDate.HasValue) { currentRow.GetCell(16).SetCellValue(member.BirthDate.Value); } // Baptis Dewasa / Anak if (!String.IsNullOrEmpty(member.ChrismationType)) { if (member.ChrismationType == "B" && member.ChrismationDate.HasValue) { currentRow.GetCell(17).SetCellValue(member.ChrismationDate.Value); } } else { if (member.ChildhoodBaptizedDate.HasValue) { currentRow.GetCell(17).SetCellValue(member.ChildhoodBaptizedDate.Value); } } // Sidi if (!String.IsNullOrEmpty(member.ChrismationType) && member.ChrismationType == "S" && member.ChrismationDate.HasValue) { currentRow.GetCell(18).SetCellValue(member.ChrismationDate.Value); } // Atestasi Masuk if (member.JoinDate.HasValue) { currentRow.GetCell(19).SetCellValue(member.JoinDate.Value); } // Atestasi Keluar if (member.ResignDate.HasValue) { currentRow.GetCell(20).SetCellValue(member.ResignDate.Value); } // Meninggal dunia if (member.DeceasedDate.HasValue) { currentRow.GetCell(21).SetCellValue(member.DeceasedDate.Value); } IEnumerable <MemberStateHistoryModel> validStateHistories = (from state in stateHistories where state.MemberId == member.Id && state.EffectiveDate >= startDatePeriod && state.EffectiveDate <= endDatePeriod select state).ToList(); if (validStateHistories != null) { // Tanggal DKH MemberStateHistoryModel dkhState = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("DKH")); if (dkhState != null) { currentRow.GetCell(22).SetCellValue(dkhState.EffectiveDate); } // Ex DKH MemberStateHistoryModel exDkhState = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("Ex.DKH")); if (exDkhState != null) { currentRow.GetCell(23).SetCellValue(exDkhState.EffectiveDate); } // Ex DKH4 MemberStateHistoryModel exDkh4State = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("Ex.DKH-4")); if (exDkh4State != null) { currentRow.GetCell(24).SetCellValue(exDkh4State.EffectiveDate); } // Status DKH dan lainnya int cellIndex = 25; foreach (MemberStateHistoryModel stateHistory in validStateHistories) { currentRow.GetCell(cellIndex).SetCellValue(stateHistory.StateCode); cellIndex++; // insufficient column in the Excel template if (cellIndex > 27) { break; } } } rowIndex++; } sheet.ForceFormulaRecalculation = true; }
/// <summary> /// Goes through the Wokrbook, optimising the cell styles /// by removing duplicate ones and ones that aren't used. /// For best results, optimise the fonts via a call to /// OptimiseFonts(HSSFWorkbook) first /// </summary> /// <param name="workbook">The workbook in which to optimise the cell styles</param> public static void OptimiseCellStyles(HSSFWorkbook workbook) { // Where each style has ended up, and if we need to // delete the record for it. Start off with no change short[] newPos = new short[workbook.Workbook.NumExFormats]; bool[] isUsed = new bool[newPos.Length]; bool[] zapRecords = new bool[newPos.Length]; for (int i = 0; i < newPos.Length; i++) { isUsed[i] = false; newPos[i] = (short)i; zapRecords[i] = false; } // Get each style record, so we can do deletes // without Getting confused ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length]; for (int i = 0; i < newPos.Length; i++) { xfrs[i] = workbook.Workbook.GetExFormatAt(i); } // Loop over each style, seeing if it is the same // as an earlier one. If it is, point users of the // later duplicate copy to the earlier one, and // mark the later one as needing deleting // Only work on user added ones, which come after 20 for (int i = 21; i < newPos.Length; i++) { // Check this one for being a duplicate // of an earlier one int earlierDuplicate = -1; for (int j = 0; j < i && earlierDuplicate == -1; j++) { ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j); if (xfCheck.Equals(xfrs[i])) { earlierDuplicate = j; } } // If we got a duplicate, mark it as such if (earlierDuplicate != -1) { newPos[i] = (short)earlierDuplicate; zapRecords[i] = true; } } // Loop over all the cells in the file, and identify any user defined // styles aren't actually being used (don't touch built-in ones) for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++) { HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum); foreach (IRow row in s) { foreach (ICell cellI in row) { HSSFCell cell = (HSSFCell)cellI; short oldXf = cell.CellValueRecord.XFIndex; isUsed[oldXf] = true; } } } // Mark any that aren't used as needing zapping for (int i = 21; i < isUsed.Length; i++) { if (!isUsed[i]) { // Un-used style, can be removed zapRecords[i] = true; newPos[i] = 0; } } // Update the new positions based on // deletes that have occurred between // the start and them // Only work on user added ones, which come after 20 for (int i = 21; i < newPos.Length; i++) { // Find the number deleted to that // point, and adjust short preDeletePos = newPos[i]; short newPosition = preDeletePos; for (int j = 0; j < preDeletePos; j++) { if (zapRecords[j]) { newPosition--; } } // Update the new position newPos[i] = newPosition; } // Zap the un-needed user style records // removing by index, because removing by object may delete // styles we did not intend to (the ones that _were_ duplicated and not the duplicates) int max = newPos.Length; int removed = 0; // to adjust index after deletion for (int i = 21; i < max; i++) { if (zapRecords[i + removed]) { workbook.Workbook.RemoveExFormatRecord(i); i--; max--; removed++; } } // Finally, update the cells to point at their new extended format records for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++) { HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum); //IEnumerator rIt = s.GetRowEnumerator(); //while (rIt.MoveNext()) foreach (IRow row in s) { //HSSFRow row = (HSSFRow)rIt.Current; //IEnumerator cIt = row.GetEnumerator(); //while (cIt.MoveNext()) foreach (ICell cell in row) { //ICell cell = (HSSFCell)cIt.Current; short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex; NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt( newPos[oldXf] ); cell.CellStyle = (newStyle); } } } }
/// <summary> /// 将excel转换为datable /// </summary> /// <param name="fileStream">excel文件流</param> /// <param name="skip">忽略处理列数</param> /// <returns></returns> public static DataTable GetDataTable(Stream fileStream, int skip = 0) { DataTable dt = new DataTable(); try { if (fileStream == null) { return(dt); } IWorkbook workbook = new HSSFWorkbook(fileStream); ISheet sheet = workbook.GetSheetAt(0); IRow firstRow = sheet.GetRow(0); if (firstRow == null) { return(dt); //空Excel文件 } int coluNum = firstRow.LastCellNum; //列数 //根据excel的列数定义列 for (int colInx = 0; colInx < coluNum; colInx++) { dt.Columns.Add(Convert.ToChar(((int)'A') + colInx).ToString()); } int startRowInx = sheet.FirstRowNum + skip; for (int rowInx = startRowInx; rowInx <= sheet.LastRowNum; rowInx++) { IRow sheetRow = sheet.GetRow(rowInx); if (sheetRow == null || sheetRow.Cells.Count <= 3) { continue; //当excel行列数小于等于3则忽略 } DataRow dataRow = dt.NewRow(); for (int colInx = 0; colInx < coluNum; colInx++) { ICell cell = sheetRow.GetCell(colInx); if (cell == null) { continue; } if (cell.CellType == CellType.NUMERIC) { //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { dataRow[colInx] = cell.DateCellValue; } else//其他数字类型 { dataRow[colInx] = cell.NumericCellValue; } } else { dataRow[colInx] = cell.ToString(); } } dt.Rows.Add(dataRow); } return(dt); } catch (Exception ex) { //日志记录点 throw ex; } finally { fileStream.Close(); fileStream.Dispose(); } }
private int ProcessTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) { ISheet sheet = workbook.GetSheetAt(sheetIndex); HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(workbook); int maxRows = sheet.LastRowNum + 1; int result = Result.NO_EVALUATIONS_FOUND; // so far String currentGroupComment = null; for (int rowIndex = SS.START_TEST_CASES_ROW_INDEX; rowIndex < maxRows; rowIndex++) { IRow r = sheet.GetRow(rowIndex); String newMarkerValue = GetMarkerColumnValue(r); if (r == null) { continue; } if (SS.TEST_CASES_END_MARKER.Equals(newMarkerValue, StringComparisonShim.InvariantCultureIgnoreCase)) { // normal exit point return(result); } if (SS.SKIP_CURRENT_TEST_CASE_MARKER.Equals(newMarkerValue, StringComparisonShim.InvariantCultureIgnoreCase)) { // currently disabled test case row continue; } if (newMarkerValue != null) { currentGroupComment = newMarkerValue; } ICell c = r.GetCell(SS.COLUMN_INDEX_EVALUATION); if (c == null || c.CellType != CellType.Formula) { continue; } ICell expectedValueCell = r.GetCell(SS.COLUMN_INDEX_EXPECTED_RESULT); String rowComment = GetRowCommentColumnValue(r); String msgPrefix = formatTestCaseDetails(this.Filename, sheetName, r.RowNum, c, currentGroupComment, rowComment); try { CellValue actualValue = Evaluator.Evaluate(c); ConfirmExpectedResult(msgPrefix, expectedValueCell, actualValue); _evaluationSuccessCount++; if (result != Result.SOME_EVALUATIONS_FAILED) { result = Result.ALL_EVALUATIONS_SUCCEEDED; } } catch (RuntimeException e) { _evaluationFailureCount++; printshortStackTrace(System.Console.Error, e); result = Result.SOME_EVALUATIONS_FAILED; } catch (AssertionException e) { _evaluationFailureCount++; printshortStackTrace(System.Console.Error, e); result = Result.SOME_EVALUATIONS_FAILED; } } throw new Exception("Missing end marker '" + SS.TEST_CASES_END_MARKER + "' on sheet '" + sheetName + "'"); }
private bool ImportData(ShippingMethod method) { bool result = false; int i = 1; try { HSSFWorkbook workbook = new HSSFWorkbook(fileUpload.UploadedFiles[0].InputStream, true); ISheet worksheet = workbook.GetSheetAt(0); if (worksheet == null) { return(false); } int iFinished = 0; bool shippingByGeoZone = (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndFixed || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight ); List <ShippingTableRate> lstTableRates = new List <ShippingTableRate>(); if (shippingMethodId > 0) { lstTableRates = ShippingTableRate.GetByMethod(method.ShippingMethodId); } for (i = 1; i <= worksheet.LastRowNum; i++) { IRow dataRow = worksheet.GetRow(i); if (dataRow != null) { string excelGeoZoneGuid = GetValueFromExcel(dataRow.GetCell(0)).Trim(); string excelShippingFee = GetValueFromExcel(dataRow.GetCell(2)).Trim(); string excelFromValue = GetValueFromExcel(dataRow.GetCell(3)).Trim(); string excelOverXValue = GetValueFromExcel(dataRow.GetCell(4)).Trim(); bool isValid = true; if (string.IsNullOrEmpty(excelShippingFee)) { isValid = false; } if (shippingByGeoZone) { if (excelGeoZoneGuid.Length != 36) { isValid = false; } if ((method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight) && string.IsNullOrEmpty(excelFromValue)) { isValid = false; } } else if (string.IsNullOrEmpty(excelFromValue)) { isValid = false; } if (!isValid) { iFinished += 1; if (iFinished >= 2) { break; } continue; } iFinished = 0; var lstShippingFee = excelShippingFee.SplitOnChar('+'); var lstFromValue = excelFromValue.SplitOnChar('+'); Guid geoZoneGuid = Guid.Empty; decimal shippingFee = Convert.ToDecimal(lstShippingFee[0]); decimal fromValue = decimal.Zero; decimal additionalFee = decimal.Zero; decimal additionalValue = decimal.Zero; if (shippingByGeoZone) { geoZoneGuid = new Guid(excelGeoZoneGuid); if ((method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight)) { fromValue = Convert.ToDecimal(lstFromValue[0]); } } else { fromValue = Convert.ToDecimal(lstFromValue[0]); } if (lstShippingFee.Count == 2 && lstFromValue.Count == 2 && (method.ShippingProvider == (int)ShippingMethodProvider.ByWeight || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight)) { additionalFee = Convert.ToDecimal(lstShippingFee[1]); additionalValue = Convert.ToDecimal(lstFromValue[1]); } int shippingTableRateId = -1; foreach (ShippingTableRate tblRate in lstTableRates) { if (shippingByGeoZone) { if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndFixed) { if (shippingFee == tblRate.ShippingFee && geoZoneGuid == tblRate.GeoZoneGuid) { tblRate.MarkAsDeleted = false; shippingTableRateId = tblRate.ShippingTableRateId; break; } } else if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal) { if (shippingFee == tblRate.ShippingFee && geoZoneGuid == tblRate.GeoZoneGuid && fromValue == tblRate.FromValue) { tblRate.MarkAsDeleted = false; shippingTableRateId = tblRate.ShippingTableRateId; break; } } else if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight) { if (shippingFee == tblRate.ShippingFee && geoZoneGuid == tblRate.GeoZoneGuid && fromValue == tblRate.FromValue) { tblRate.MarkAsDeleted = false; shippingTableRateId = tblRate.ShippingTableRateId; break; } } } else { if (shippingFee == tblRate.ShippingFee && fromValue == tblRate.FromValue) { tblRate.MarkAsDeleted = false; shippingTableRateId = tblRate.ShippingTableRateId; break; } } } ShippingTableRate tableRate = null; if (shippingTableRateId > 0) { tableRate = new ShippingTableRate(shippingTableRateId); } else { tableRate = new ShippingTableRate(); tableRate.ShippingMethodId = method.ShippingMethodId; } if (shippingByGeoZone) { tableRate.GeoZoneGuid = geoZoneGuid; if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight) { tableRate.FromValue = fromValue; } } else { tableRate.FromValue = fromValue; } tableRate.FreeShippingOverXValue = 0; if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndFixed || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight) { if (!string.IsNullOrEmpty(excelOverXValue)) { tableRate.FreeShippingOverXValue = Convert.ToDecimal(excelOverXValue); } } tableRate.ShippingFee = shippingFee; tableRate.AdditionalFee = additionalFee; tableRate.AdditionalValue = additionalValue; tableRate.Save(); result = true; } } foreach (ShippingTableRate tblRate in lstTableRates) { if (tblRate.MarkAsDeleted) { ShippingTableRate.Delete(tblRate.ShippingTableRateId); } } } catch (Exception ex) { result = false; message.ErrorMessage = ex.Message; log.Error(ex.Message); } return(result); }
/// <summary> /// 使用第三方插件NPOI读取Excel内容到DataTable,默认读取Excel文件的第一个Sheet /// </summary> /// <param name="filePath">文件路径</param> /// <returns></returns> public DataTable GetDataFromExcel(string filePath) { ISheet sheet = null;; if (filePath.ToLower().EndsWith("xlsx")) { XSSFWorkbook xssFWorkbook; using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { xssFWorkbook = new XSSFWorkbook(file); } sheet = xssFWorkbook.GetSheetAt(0); } else if (filePath.ToLower().EndsWith("xls")) { HSSFWorkbook hssfWorkbook; using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfWorkbook = new HSSFWorkbook(file); } sheet = hssfWorkbook.GetSheetAt(0); } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); int t = 0; while (rows.MoveNext()) { IRow row = rows.Current as IRow; int cellCount = row.LastCellNum; if (cellCount == -1) { continue; } DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); if (t == 0) { DataColumn column = null; if (cell == null) { column = new DataColumn(string.Empty, typeof(string)); } else { column = new DataColumn(cell.ToString().Trim(), typeof(string)); } dt.Columns.Add(column); } else { if (i > dt.Columns.Count - 1) { break; } if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString().Trim(); } } } if (t > 0) { dt.Rows.Add(dr); } t++; } return(dt); }
/// <summary> /// Goes through the Wokrbook, optimising the cell styles /// by removing duplicate ones. /// For best results, optimise the fonts via a call to /// OptimiseFonts(HSSFWorkbook) first /// </summary> /// <param name="workbook">The workbook in which to optimise the cell styles</param> public static void OptimiseCellStyles(HSSFWorkbook workbook) { // Where each style has ended up, and if we need to // delete the record for it. Start off with no change short[] newPos = new short[workbook.Workbook.NumExFormats]; bool[] zapRecords = new bool[newPos.Length]; for (int i = 0; i < newPos.Length; i++) { newPos[i] = (short)i; zapRecords[i] = false; } // Get each style record, so we can do deletes // without Getting confused ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length]; for (int i = 0; i < newPos.Length; i++) { xfrs[i] = workbook.Workbook.GetExFormatAt(i); } // Loop over each style, seeing if it is the same // as an earlier one. If it is, point users of the // later duplicate copy to the earlier one, and // mark the later one as needing deleting // Only work on user added ones, which come after 20 for (int i = 21; i < newPos.Length; i++) { // Check this one for being a duplicate // of an earlier one int earlierDuplicate = -1; for (int j = 0; j < i && earlierDuplicate == -1; j++) { ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j); if (xfCheck.Equals(xfrs[i])) { earlierDuplicate = j; } } // If we got a duplicate, mark it as such if (earlierDuplicate != -1) { newPos[i] = (short)earlierDuplicate; zapRecords[i] = true; } } // Update the new positions based on // deletes that have occurred between // the start and them // Only work on user added ones, which come after 20 for (int i = 21; i < newPos.Length; i++) { // Find the number deleted to that // point, and adjust short preDeletePos = newPos[i]; short newPosition = preDeletePos; for (int j = 0; j < preDeletePos; j++) { if (zapRecords[j]) { newPosition--; } } // Update the new position newPos[i] = newPosition; } // Zap the un-needed user style records for (int i = 21; i < newPos.Length; i++) { if (zapRecords[i]) { workbook.Workbook.RemoveExFormatRecord( xfrs[i] ); } } // Finally, update the cells to point at // their new extended format records for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++) { HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum); IEnumerator rIt = s.GetRowEnumerator(); while (rIt.MoveNext()) { HSSFRow row = (HSSFRow)rIt.Current; IEnumerator cIt = row.GetCellEnumerator(); while (cIt.MoveNext()) { Cell cell = (HSSFCell)cIt.Current; short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex; NPOI.SS.UserModel.CellStyle newStyle = workbook.GetCellStyleAt( newPos[oldXf] ); cell.CellStyle = (newStyle); } } } }
/// <summary> /// 将excel中的数据集转成datatable集合 /// </summary> /// <param name="excelFileStream">excel文件流</param> /// <param name="sheetIndex">起始标签页码:从0开始</param> /// <returns></returns> public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, out string strMsg) { try { strMsg = string.Empty; HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream); HSSFSheet sheet = workbook.GetSheetAt(sheetIndex); DataTable table = new DataTable(); #region 构造datatable的列 for (int i = sheet.GetRow(sheet.FirstRowNum).FirstCellNum; i < sheet.GetRow(sheet.FirstRowNum).LastCellNum; i++) { string title = sheet.GetRow(sheet.FirstRowNum).GetCell(i).StringCellValue; DataColumn col = new DataColumn(title.Trim()); table.Columns.Add(col); } #endregion 构造datatable的列 System.Text.RegularExpressions.Regex re = new System.Text.RegularExpressions.Regex(@"\n"); //sheet.LastRowNum:标签页中的行数 for (int i = (sheet.FirstRowNum); i <= sheet.LastRowNum; i++) { #region 构造datatable DataRow dr = table.NewRow(); dr.BeginEdit(); HSSFRow row = sheet.GetRow(i); //row.LastCellNum:一行中的单元格数据 for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { HSSFCell cell = row.GetCell(j); if (cell == null) { continue; } #region 判断单元格类型 switch (cell.CellType) { case HSSFCellType.BLANK: dr[j] = ""; break; case HSSFCellType.BOOLEAN: dr[j] = cell.BooleanCellValue; break; case HSSFCellType.NUMERIC: dr[j] = re.Replace(cell.ToString(), "").Trim(); break; case HSSFCellType.STRING: dr[j] = (re.Replace(cell.StringCellValue, "").Replace(".", ".")).Trim(); break; case HSSFCellType.ERROR: dr[j] = cell.ErrorCellValue; break; case HSSFCellType.FORMULA: dr[j] = cell.NumericCellValue; break; default: dr[j] = cell.CellFormula; break; } #endregion 判断单元格类型 } dr.EndEdit(); table.Rows.Add(dr); #endregion 构造datatable } excelFileStream.Close(); workbook = null; sheet = null; return(table); } catch (Exception ex) { strMsg = ex.Message; return(null); } }
public bool ImportExcel(Stream stream, out string msg) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream); List <TakeCheck> list = new List <TakeCheck>(); using (Sheet sheet = hssfworkbook.GetSheetAt(0)) { TakeCheck takecheck; int r = 0; int v = 0; //总条目 int rowCount = sheet.LastRowNum; Expression <Func <TakeCheck> > properties = (() => new TakeCheck() { Id = 0, StoreName = "", Account = "", TakeOut = 0, Bank = "", BankNo = "" }); var dict = properties.GetPropertyWithValue(); Row row; Dictionary <string, string> di = null; for (int i = sheet.FirstRowNum + 2; i <= rowCount; i++) { row = sheet.GetRow(i); if (row != null) { r = 0; foreach (var item in dict.Keys) { Cell cell = row.GetCell(r); if (cell != null) { if (item == "Id") { if (int.TryParse(cell.ToString(), out v)) { takecheck = Repository.Get(v); if (takecheck == null) { msg = "第" + i + "记录不存在"; return(false); } if (takecheck.ConfirmTime.HasValue) { continue; } list.Add(takecheck); di = ExpressionHelper.GetProperties <TakeCheck>(takecheck); } else { msg = "第" + i + "行序号有误"; return(false); } } else { if (cell.ToString() != di[item]) { msg = "第" + i + "行数据有误"; return(false); } } } r++; } } } } if (list.Count > 0) { using (var tran = new TransactionScope()) { foreach (var item in list) { UnitWork.RegisterDirty(item, () => new TakeCheck { ConfirmTime = DateTime.Now }); } UnitWork.Commit(); tran.Complete(); } } msg = "ok"; return(true); }
public ErrorObj GetErrorJson() { var mse = new ErrorObj(); mse.Form = new Dictionary <string, string>(); var err = ErrorListVM?.EntityList?.Where(x => x.Index == 0).FirstOrDefault()?.Message; if (string.IsNullOrEmpty(err)) { var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault(); hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo); var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); List <ExcelPropety> excelPropetys = new List <ExcelPropety>(); for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template); excelPropetys.Add(ep); } int columnCount = excelPropetys.Count; //int excelPropetyCount = excelPropetys.Count; var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault(); if (dynamicColumn != null) { columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1; } ISheet sheet = hssfworkbook.GetSheetAt(0); var errorStyle = hssfworkbook.CreateCellStyle(); IFont f = hssfworkbook.CreateFont(); f.Color = HSSFColor.Red.Index; errorStyle.SetFont(f); errorStyle.IsLocked = true; foreach (var e in ErrorListVM?.EntityList) { if (e.Index > 0) { var c = sheet.GetRow((int)(e.Index - 1)).CreateCell(columnCount); c.CellStyle = errorStyle; c.SetCellValue(e.Message); } } MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); ms.Position = 0; FileAttachmentVM vm = new FileAttachmentVM(); vm.CopyContext(this); vm.Entity.FileName = "Error-" + fa.FileName; vm.Entity.Length = ms.Length; vm.Entity.UploadTime = DateTime.Now; vm.Entity.SaveFileMode = ConfigInfo.FileUploadOptions.SaveFileMode; vm = FileHelper.GetFileByteForUpload(vm, ms, ConfigInfo, vm.Entity.FileName, null, null); vm.Entity.IsTemprory = true; if ((!string.IsNullOrEmpty(vm.Entity.Path) && (vm.Entity.SaveFileMode == SaveFileModeEnum.Local || vm.Entity.SaveFileMode == SaveFileModeEnum.DFS)) || (vm.Entity.FileData != null && vm.Entity.SaveFileMode == SaveFileModeEnum.Database)) { vm.DoAdd(); } ms.Close(); ms.Dispose(); err = "导入时发生错误"; mse.Form.Add("Entity.Import", err); mse.Form.Add("Entity.ErrorFileId", vm.Entity.ID.ToString()); } else { mse.Form.Add("Entity.Import", err); } return(mse); }
public IActionResult ImportExport(IFormFile files) { ArrayList outArr = new ArrayList(); ArrayList stockSymbol = new ArrayList(); IFormFile file = Request.Form.Files[0]; if (file != null) { string folderName = "Upload"; string webRootPath = _hostingEnvironment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); StringBuilder sb = new StringBuilder(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, file.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; sb.Append("<table class='table table-hover'><tr>"); for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } outArr.Add(cell.ToString()); // Add to the array sb.Append("<th>" + cell.ToString() + "</th>"); } sb.Append("</tr>"); sb.AppendLine("<tr>"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { outArr.Add(row.GetCell(j).ToString()); sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); //Check if it's a unique stock Symbol (if so add to stockSymbol arrayList) if (j == 1 && !stockSymbol.Contains(row.GetCell(j).ToString())) { stockSymbol.Add(row.GetCell(j).ToString()); } } } sb.AppendLine("</tr>"); } sb.Append("</table>"); stream.Close(); // Close the reading stream } } Debug.WriteLine(outArr.ToArray()); DataSaveWrite.WriteDataToFile(outArr, "stockInfo"); // Write it to a file DataSaveWrite.WriteDataToFile(stockSymbol, "stockList"); return(this.Content(sb.ToString())); } return(new EmptyResult()); }
/// <summary> /// 读取模版中的数据 /// </summary> private void DoMapList() { try { Template.InitExcelData(); Template.InitCustomFormat(); TemplateData = new List <T>(); hssfworkbook = new HSSFWorkbook(); if (UploadFileId == null) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = "请上传模板文件" }); return; } var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault(); hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo); if (ValidityTemplateType && hssfworkbook.GetSheetAt(1).GetRow(0).Cells[2].ToString() != typeof(T).Name) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = "错误的模板" }); return; } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); //所有ExcelPropety属性 List <ExcelPropety> excelPropetys = new List <ExcelPropety>(); for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template); excelPropetys.Add(ep); } #region 验证模版正确性 add by dufei //取得列数 int columnCount = excelPropetys.Count; //int excelPropetyCount = excelPropetys.Count; var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault(); if (dynamicColumn != null) { columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1; //excelPropetyCount = excelPropetyCount + dynamicColumn.DynamicColumns.Count - 1; } int pIndex = 0; var cells = sheet.GetRow(0).Cells; if (columnCount != cells.Count) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = "请下载新模板或上传符合当前功能的模板" }); return; } else { for (int i = 0; i < cells.Count; i++) { if (excelPropetys[pIndex].DataType != ColumnDataType.Dynamic) { if (cells[i].ToString().Trim('*') != excelPropetys[pIndex].ColumnName) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = "请下载新模板或上传符合当前功能的模板" }); return; } pIndex++; } else { var listDynamicColumns = excelPropetys[i].DynamicColumns; int dcCount = listDynamicColumns.Count; for (int dclIndex = 0; dclIndex < dcCount; dclIndex++) { if (cells[i].ToString().Trim('*') != listDynamicColumns[dclIndex].ColumnName) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = "请下载新模板或上传符合当前功能的模板" }); break; } i = i + 1; } i = i - 1; pIndex++; } } } #endregion int rowIndex = 2; rows.MoveNext(); while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; if (IsEmptyRow(row, columnCount)) { return; } T result = new T(); int propetyIndex = 0; for (int i = 0; i < columnCount; i++) { ExcelPropety excelPropety = CopyExcelPropety(excelPropetys[propetyIndex]); //excelPropetys[propetyIndex]; var pts = propetys[propetyIndex]; string value = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(); if (excelPropety.DataType == ColumnDataType.Dynamic) { int dynamicColCount = excelPropety.DynamicColumns.Count(); for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++) { //验证数据类型并添加错误信息 excelPropety.DynamicColumns[dynamicColIndex].ValueValidity(row.GetCell(i + dynamicColIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(), ErrorListVM.EntityList, rowIndex); } i = i + dynamicColCount - 1; } else { excelPropety.ValueValidity(value, ErrorListVM.EntityList, rowIndex); } if (ErrorListVM.EntityList.Count == 0) { pts.SetValue(result, excelPropety); } propetyIndex++; } result.ExcelIndex = rowIndex; TemplateData.Add(result); rowIndex++; } return; } catch { ErrorListVM.EntityList.Add(new ErrorMessage { Message = "请下载新模板或上传符合当前功能的模板" }); //ErrorListVM.ErrorList.Add(new ErrorMessage { Message = ex.Message }); } return; }
public int ExcelToDataTable(ref DataTable dt, string sheetName) { try { if (dt == null) { return(0); } dt.Columns.Clear(); dt.Rows.Clear(); dt.Clear(); //先清空表 HSSFWorkbook Workbook = NPOIOpenExcel(FileName); //打开工作薄 ISheet sheet = null; if (sheetName != null) { sheet = Workbook.GetSheet(sheetName); } if (sheet == null) { sheet = Workbook.GetSheetAt(0); } if (sheet == null) { return(0); } IRow firstRow = sheet.GetRow(0); if (firstRow == null) { return(0); } int nColCount = firstRow.LastCellNum; int nRowCount = sheet.LastRowNum + 1; for (int i = 0; i < nColCount; i++) { string strValue = firstRow.GetCell(i).ToString(); if (strValue != null && strValue.Trim() != "") { dt.Columns.Add(strValue); } } for (int i = 1; i < nRowCount; i++) { if (sheet.GetRow(i) == null || sheet.GetRow(i).GetCell(0) == null || sheet.GetRow(i).GetCell(0).ToString().Trim() == "") //排除null { continue; } DataRow dr = dt.NewRow(); for (int j = 0; j < nColCount; j++) { string strField = firstRow.GetCell(j).ToString(); if (sheet.GetRow(i).GetCell(j) == null) //排除null { continue; } dr[strField] = sheet.GetRow(i).GetCell(j).ToString(); } dt.Rows.Add(dr); } } catch (Exception ex) { throw new Exception($"读取Excel文件到DataTable出错:{ex.Message}"); } return(1); }
protected void btn_Import_Click_Click(object sender, EventArgs e) { List <string> listc = new List <string>(); List <string> listm = new List <string>(); string FilePath = @"E:\利润及利润分配表\"; if (!Directory.Exists(FilePath)) { Directory.CreateDirectory(FilePath); } //将文件上传到服务器 HttpPostedFile UserHPF = FileUpload1.PostedFile; try { string fileContentType = UserHPF.ContentType;// 获取客户端发送的文件的 MIME 内容类型 if (fileContentType == "application/vnd.ms-excel") { if (UserHPF.ContentLength > 0) { UserHPF.SaveAs(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName));//将上传的文件存放在指定的文件夹中 } } else { Response.Write("<script>alert('文件类型不符合要求,请您核对后重新上传!');</script>"); return; } } catch { Response.Write("<script>alert('文件上传过程中出现错误!');</script>"); return; } using (FileStream fs = File.OpenRead(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName))) { //根据文件流创建一个workbook IWorkbook wk = new HSSFWorkbook(fs); //获取第一个工作表 ISheet sheet = wk.GetSheetAt(0); //循环读取每一行数据,由于execel有列名以及序号,从1开始 string sqlc = ""; string sqlm = ""; string ncs = "本期数"; string qms = "本年累计数"; IRow row1 = sheet.GetRow(44); ICell cell0 = row1.GetCell(0); string rqbhc = cell0.StringCellValue.ToString().Trim(); string rqbhm = cell0.StringCellValue.ToString().Trim();//得到修改人员编码 string sqlTextchk = "select * from TBFM_LRFP where RQBH='" + cell0.StringCellValue.ToString().Trim() + "'"; System.Data.DataTable dtchk = DBCallCommon.GetDTUsingSqlText(sqlTextchk); if (dtchk.Rows.Count > 0) { Response.Write("<script>alert('日期编号已存在!');</script>"); return; } sqlc = "'" + rqbhc + "','" + ncs + "'"; sqlm = "'" + rqbhm + "','" + qms + "'"; IRow row4 = sheet.GetRow(47); ICell cell24 = row4.GetCell(2); ICell cell34 = row4.GetCell(3); sqlc += ",'" + cell24.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell34.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row5 = sheet.GetRow(48); ICell cell25 = row5.GetCell(2); ICell cell35 = row5.GetCell(3); sqlc += ",'" + cell25.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell35.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row6 = sheet.GetRow(49); ICell cell26 = row6.GetCell(2); ICell cell36 = row6.GetCell(3); sqlc += ",'" + cell26.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell36.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row7 = sheet.GetRow(50); ICell cell27 = row7.GetCell(2); ICell cell37 = row7.GetCell(3); sqlc += ",'" + cell27.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell37.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row8 = sheet.GetRow(51); ICell cell28 = row8.GetCell(2); ICell cell38 = row8.GetCell(3); sqlc += ",'" + cell28.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell38.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row9 = sheet.GetRow(52); ICell cell29 = row9.GetCell(2); ICell cell39 = row9.GetCell(3); sqlc += ",'" + cell29.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell39.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row10 = sheet.GetRow(53); ICell cell210 = row10.GetCell(2); ICell cell310 = row10.GetCell(3); sqlc += ",'" + cell210.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell310.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row11 = sheet.GetRow(54); ICell cell211 = row11.GetCell(2); ICell cell311 = row11.GetCell(3); sqlc += ",'" + cell211.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell311.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row12 = sheet.GetRow(55); ICell cell212 = row12.GetCell(2); ICell cell312 = row12.GetCell(3); sqlc += ",'" + cell212.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell312.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row13 = sheet.GetRow(56); ICell cell213 = row13.GetCell(2); ICell cell313 = row13.GetCell(3); sqlc += ",'" + cell213.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell313.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row14 = sheet.GetRow(57); ICell cell214 = row14.GetCell(2); ICell cell314 = row14.GetCell(3); sqlc += ",'" + cell214.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell314.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row15 = sheet.GetRow(58); ICell cell215 = row15.GetCell(2); ICell cell315 = row15.GetCell(3); sqlc += ",'" + cell215.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell315.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row16 = sheet.GetRow(58); ICell cell216 = row16.GetCell(2); ICell cell316 = row16.GetCell(3); sqlc += ",'" + cell216.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell316.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row17 = sheet.GetRow(60); ICell cell217 = row17.GetCell(2); ICell cell317 = row17.GetCell(3); sqlc += ",'" + cell217.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell317.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row18 = sheet.GetRow(61); ICell cell218 = row18.GetCell(2); ICell cell318 = row18.GetCell(3); sqlc += ",'" + cell218.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell318.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row19 = sheet.GetRow(62); ICell cell219 = row19.GetCell(2); ICell cell319 = row19.GetCell(3); sqlc += ",'" + cell219.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell319.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row20 = sheet.GetRow(63); ICell cell220 = row20.GetCell(2); ICell cell320 = row20.GetCell(3); sqlc += ",'" + cell220.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell320.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row21 = sheet.GetRow(64); ICell cell221 = row21.GetCell(2); ICell cell321 = row21.GetCell(3); sqlc += ",'" + cell221.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell321.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row22 = sheet.GetRow(65); ICell cell222 = row22.GetCell(2); ICell cell322 = row22.GetCell(3); sqlc += ",'" + cell222.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell322.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row23 = sheet.GetRow(66); ICell cell223 = row23.GetCell(2); ICell cell323 = row23.GetCell(3); sqlc += ",'" + cell223.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell323.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row24 = sheet.GetRow(67); ICell cell224 = row24.GetCell(2); ICell cell324 = row24.GetCell(3); sqlc += ",'" + cell224.NumericCellValue.ToString("0.00").Trim() + "'"; sqlm += ",'" + cell324.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row27 = sheet.GetRow(70); ICell cell327 = row27.GetCell(3); sqlm += ",'" + cell327.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row28 = sheet.GetRow(71); ICell cell328 = row28.GetCell(3); sqlm += ",'" + cell328.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row29 = sheet.GetRow(72); ICell cell329 = row29.GetCell(3); sqlm += ",'" + cell329.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row30 = sheet.GetRow(73); ICell cell230 = row30.GetCell(2); ICell cell330 = row30.GetCell(3); sqlm += ",'" + cell330.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row31 = sheet.GetRow(74); ICell cell331 = row31.GetCell(3); sqlm += ",'" + cell331.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row32 = sheet.GetRow(75); ICell cell332 = row32.GetCell(3); sqlm += ",'" + cell332.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row33 = sheet.GetRow(76); ICell cell333 = row33.GetCell(3); sqlm += ",'" + cell333.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row34 = sheet.GetRow(77); ICell cell334 = row34.GetCell(3); sqlm += ",'" + cell334.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row35 = sheet.GetRow(78); ICell cell235 = row35.GetCell(2); ICell cell335 = row35.GetCell(3); sqlm += ",'" + cell335.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row36 = sheet.GetRow(79); ICell cell336 = row36.GetCell(3); sqlm += ",'" + cell336.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row37 = sheet.GetRow(80); ICell cell237 = row37.GetCell(2); ICell cell337 = row37.GetCell(3); sqlm += ",'" + cell337.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row38 = sheet.GetRow(81); ICell cell338 = row38.GetCell(3); sqlm += ",'" + cell338.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row39 = sheet.GetRow(82); ICell cell239 = row39.GetCell(2); ICell cell339 = row39.GetCell(3); sqlm += ",'" + cell339.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row40 = sheet.GetRow(83); ICell cell340 = row40.GetCell(3); sqlm += ",'" + cell340.NumericCellValue.ToString("0.00").Trim() + "'"; IRow row41 = sheet.GetRow(84); ICell cell341 = row41.GetCell(3); sqlm += ",'" + cell341.NumericCellValue.ToString("0.00").Trim() + "'"; #region 存入数据库 string sqlTxtc = string.Format("insert into TBFM_LRFP({0}) values({1})", "RQBH,LRFP_TYPE,LRFP_YYSR,LRFP_YYSR_ZYSR,LRFP_YYSR_QTSR,LRFP_YYSR_JYCB,LRFP_YYSR_ZYCB,LRFP_YYSR_QTCB,LRFP_YYSR_SJFJ,LRFP_YYSR_XSFY,LRFP_YYSR_GLFY,LRFP_YYSR_CWFY,LRFP_YYSR_JZSS,LRFP_YYSR_JZBD,LRFP_YYSR_TZSY,LRFP_YYSR_LYHY,LRFP_YYLR,LRFP_YYLR_YWSR,LRFP_YYLR_YWZC,LRFP_YYLR_FLDSS,LRFP_LRZE,LRFP_LRZE_SDSF,LRFP_JLR", sqlc); string sqlTxtm = string.Format("insert into TBFM_LRFP({0}) values({1})", "RQBH,LRFP_TYPE,LRFP_YYSR,LRFP_YYSR_ZYSR,LRFP_YYSR_QTSR,LRFP_YYSR_JYCB,LRFP_YYSR_ZYCB,LRFP_YYSR_QTCB,LRFP_YYSR_SJFJ,LRFP_YYSR_XSFY,LRFP_YYSR_GLFY,LRFP_YYSR_CWFY,LRFP_YYSR_JZSS,LRFP_YYSR_JZBD,LRFP_YYSR_TZSY,LRFP_YYSR_LYHY,LRFP_YYLR,LRFP_YYLR_YWSR,LRFP_YYLR_YWZC,LRFP_YYLR_FLDSS,LRFP_LRZE,LRFP_LRZE_SDSF,LRFP_JLR,LRFP_NCWFP,LRFP_QTZR,LRFP_KGFP,LRFP_KGFP_FDYYGJ,LRFP_KGFP_FDGY,LRFP_KGFP_JLFL,LRFP_KGFP_CBJJ,LRFP_KGFP_QYFZ,LRFP_KGFP_LRGH,LRFP_KGTZFP,LRFP_KGTZFP_YFYXG,LRFP_KGTZFP_RYYY,LRFP_KGTZFP_YFPTG,LRFP_KGTZFP_ZZZB,LRFP_WFPLR", sqlm); listc.Add(sqlTxtc); listm.Add(sqlTxtm); #endregion } DBCallCommon.ExecuteTrans(listc); DBCallCommon.ExecuteTrans(listm); foreach (string fileName in Directory.GetFiles(FilePath))//清空该文件夹下的文件 { string newName = fileName.Substring(fileName.LastIndexOf("\\") + 1); System.IO.File.Delete(FilePath + "\\" + newName);//删除文件下储存的文件 } bindGrid(); Response.Redirect(Request.Url.ToString()); }
/// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable Import(string strFileName, string sheetName) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } HSSFSheet sheet = null; if (!string.IsNullOrEmpty(sheetName)) { sheet = hssfworkbook.GetSheet(sheetName) as HSSFSheet; } else { sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet; } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = sheet.GetRow(0) as HSSFRow; int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { HSSFCell cell = headerRow.GetCell(j) as HSSFCell; dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i) as HSSFRow; DataRow dataRow = dt.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { if (row.GetCell(j).CellType == CellType.NUMERIC) { if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else { dataRow[j] = row.GetCell(j).ToString(); } } else if (row.GetCell(j).CellType == CellType.FORMULA) { dataRow[j] = row.GetCell(j).NumericCellValue.ToString(); } else { dataRow[j] = row.GetCell(j).ToString(); } } } } dt.Rows.Add(dataRow); } return(dt); }
private void ExportDataItem(string sqltext, string pid) { string filename = "利润分配表.xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("利润分配表.xls"))) { IWorkbook wk = new HSSFWorkbook(fs);//创建workbook对象 ISheet sheet0 = wk.GetSheetAt(0); string sqltextc = sqltext + "LRFP_TYPE='本期数'"; string sqltextm = sqltext + "LRFP_TYPE='本年累计数'"; System.Data.DataTable dtc = DBCallCommon.GetDTUsingSqlText(sqltextc); System.Data.DataTable dtm = DBCallCommon.GetDTUsingSqlText(sqltextm); DataRow drc = dtc.Rows[0]; DataRow drm = dtm.Rows[0]; IRow row1 = sheet0.GetRow(1);//创建行 row1.GetCell(0).SetCellValue(drc["RQBH"].ToString()); IRow row4 = sheet0.GetRow(4);//创建行 row4.GetCell(2).SetCellValue(drc["LRFP_YYSR"].ToString()); row4.GetCell(3).SetCellValue(drm["LRFP_YYSR"].ToString()); IRow row5 = sheet0.GetRow(5); row5.GetCell(2).SetCellValue(drc["LRFP_YYSR_ZYSR"].ToString()); row5.GetCell(3).SetCellValue(drm["LRFP_YYSR_ZYSR"].ToString()); IRow row6 = sheet0.GetRow(6); row6.GetCell(2).SetCellValue(drc["LRFP_YYSR_QTSR"].ToString()); row6.GetCell(3).SetCellValue(drm["LRFP_YYSR_QTSR"].ToString()); IRow row7 = sheet0.GetRow(7); row7.GetCell(2).SetCellValue(drc["LRFP_YYSR_JYCB"].ToString()); row7.GetCell(3).SetCellValue(drm["LRFP_YYSR_JYCB"].ToString()); IRow row8 = sheet0.GetRow(8); row8.GetCell(2).SetCellValue(drc["LRFP_YYSR_ZYCB"].ToString()); row8.GetCell(3).SetCellValue(drm["LRFP_YYSR_ZYCB"].ToString()); IRow row9 = sheet0.GetRow(9); row9.GetCell(2).SetCellValue(drc["LRFP_YYSR_QTCB"].ToString()); row9.GetCell(3).SetCellValue(drm["LRFP_YYSR_QTCB"].ToString()); IRow row10 = sheet0.GetRow(10); row10.GetCell(2).SetCellValue(drc["LRFP_YYSR_SJFJ"].ToString()); row10.GetCell(3).SetCellValue(drm["LRFP_YYSR_SJFJ"].ToString()); IRow row11 = sheet0.GetRow(11); row11.GetCell(2).SetCellValue(drc["LRFP_YYSR_XSFY"].ToString()); row11.GetCell(3).SetCellValue(drm["LRFP_YYSR_XSFY"].ToString()); IRow row12 = sheet0.GetRow(12); row12.GetCell(2).SetCellValue(drc["LRFP_YYSR_XSFY"].ToString()); row12.GetCell(3).SetCellValue(drm["LRFP_YYSR_XSFY"].ToString()); IRow row13 = sheet0.GetRow(13); row13.GetCell(2).SetCellValue(drc["LRFP_YYSR_CWFY"].ToString()); row13.GetCell(3).SetCellValue(drm["LRFP_YYSR_CWFY"].ToString()); IRow row14 = sheet0.GetRow(14); row14.GetCell(2).SetCellValue(drc["LRFP_YYSR_JZSS"].ToString()); row14.GetCell(3).SetCellValue(drm["LRFP_YYSR_JZSS"].ToString()); IRow row15 = sheet0.GetRow(15); row15.GetCell(2).SetCellValue(drc["LRFP_YYSR_JZBD"].ToString()); row15.GetCell(3).SetCellValue(drm["LRFP_YYSR_JZBD"].ToString()); IRow row16 = sheet0.GetRow(16); row16.GetCell(2).SetCellValue(drc["LRFP_YYSR_TZSY"].ToString()); row16.GetCell(3).SetCellValue(drm["LRFP_YYSR_TZSY"].ToString()); IRow row17 = sheet0.GetRow(17); row17.GetCell(2).SetCellValue(drc["LRFP_YYSR_LYHY"].ToString()); row17.GetCell(3).SetCellValue(drm["LRFP_YYSR_LYHY"].ToString()); IRow row18 = sheet0.GetRow(18); row18.GetCell(2).SetCellValue(drc["LRFP_YYLR"].ToString()); row18.GetCell(3).SetCellValue(drm["LRFP_YYLR"].ToString()); IRow row19 = sheet0.GetRow(19); row19.GetCell(2).SetCellValue(drc["LRFP_YYLR_YWSR"].ToString()); row19.GetCell(3).SetCellValue(drm["LRFP_YYLR_YWSR"].ToString()); IRow row20 = sheet0.GetRow(20); row20.GetCell(2).SetCellValue(drc["LRFP_YYLR_YWZC"].ToString()); row20.GetCell(3).SetCellValue(drm["LRFP_YYLR_YWZC"].ToString()); IRow row21 = sheet0.GetRow(21); row21.GetCell(2).SetCellValue(drc["LRFP_YYLR_FLDSS"].ToString()); row21.GetCell(3).SetCellValue(drm["LRFP_YYLR_FLDSS"].ToString()); IRow row22 = sheet0.GetRow(22); row22.GetCell(2).SetCellValue(drc["LRFP_LRZE"].ToString()); row22.GetCell(3).SetCellValue(drm["LRFP_LRZE"].ToString()); IRow row23 = sheet0.GetRow(23); row23.GetCell(2).SetCellValue(drc["LRFP_LRZE_SDSF"].ToString()); row23.GetCell(3).SetCellValue(drm["LRFP_LRZE_SDSF"].ToString()); IRow row24 = sheet0.GetRow(24); row24.GetCell(2).SetCellValue(drc["LRFP_JLR"].ToString()); row24.GetCell(3).SetCellValue(drm["LRFP_JLR"].ToString()); IRow row27 = sheet0.GetRow(27); row27.GetCell(3).SetCellValue(drm["LRFP_NCWFP"].ToString()); IRow row28 = sheet0.GetRow(28); row28.GetCell(3).SetCellValue(drm["LRFP_QTZR"].ToString()); IRow row29 = sheet0.GetRow(29); row29.GetCell(3).SetCellValue(drm["LRFP_KGFP"].ToString()); IRow row30 = sheet0.GetRow(30); row30.GetCell(3).SetCellValue(drm["LRFP_KGFP_FDYYGJ"].ToString()); IRow row31 = sheet0.GetRow(31); row31.GetCell(3).SetCellValue(drm["LRFP_KGFP_FDGY"].ToString()); IRow row32 = sheet0.GetRow(32); row32.GetCell(3).SetCellValue(drm["LRFP_KGFP_JLFL"].ToString()); IRow row33 = sheet0.GetRow(33); row33.GetCell(3).SetCellValue(drm["LRFP_KGFP_CBJJ"].ToString()); IRow row34 = sheet0.GetRow(34); row34.GetCell(3).SetCellValue(drm["LRFP_KGFP_QYFZ"].ToString()); IRow row35 = sheet0.GetRow(35); row35.GetCell(3).SetCellValue(drm["LRFP_KGFP_LRGH"].ToString()); IRow row36 = sheet0.GetRow(36); row36.GetCell(3).SetCellValue(drm["LRFP_KGTZFP"].ToString()); IRow row37 = sheet0.GetRow(37); row37.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_YFYXG"].ToString()); IRow row38 = sheet0.GetRow(38); row38.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_RYYY"].ToString()); IRow row39 = sheet0.GetRow(39); row39.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_YFPTG"].ToString()); IRow row40 = sheet0.GetRow(40); row40.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_ZZZB"].ToString()); IRow row41 = sheet0.GetRow(41); row41.GetCell(3).SetCellValue(drm["LRFP_WFPLR"].ToString()); sheet0.ForceFormulaRecalculation = true; MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
private void btn_ExportCustflowReport_Click(object sender, EventArgs e) { FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog(); if (folderBrowserDialog.ShowDialog() != DialogResult.OK) { return; } string selectedPath = folderBrowserDialog.SelectedPath; string text = selectedPath + str_file_location + str_file_name + _strFromDate.Replace("-", "") + "-" + _strToDate.Replace("-", "") + str_file_type; FileInfo file = new FileInfo(text); int num = 2; if (!File.Exists(text)) { wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); sh = (HSSFSheet)wb.CreateSheet("Sheet1"); for (int i = 0; i < dataGridView2.RowCount + num; i++) { IRow row = sh.CreateRow(i); for (int j = 0; j < dataGridView2.ColumnCount; j++) { row.CreateCell(j); } } using (FileStream @out = new FileStream(text, FileMode.Create, FileAccess.Write)) { wb.Write(@out); } } if (!IsFileLocked(file)) { using (FileStream s = new FileStream(text, FileMode.Open, FileAccess.Read)) { wb = new HSSFWorkbook(s); for (int k = 0; k < wb.Count; k++) { lst_Sheet.Add(wb.GetSheetAt(k).SheetName); } } } if (!IsFileLocked(file)) { sh = (HSSFSheet)wb.GetSheet(lst_Sheet[0]); int l = 0; string[] array = new string[9] { "日期:", _strFromDate + "~" + _strToDate, "特定會員:", _member_name, "會員類型:", _member_type, "會員狀態:", _member_status, "" }; string[] array2 = new string[9] { "購買會員", "銷售總額(原始)", "現金收款", "賒帳金額", "找零", "退款金額", "總消費次", "銷售品項", "銷售數量" }; for (; l < num; l++) { for (int m = 0; m < dataGridView2.ColumnCount; m++) { if (sh.GetRow(l).GetCell(m) == null) { sh.GetRow(l).CreateCell(m); } if (l == 0) { sh.GetRow(l).GetCell(m).SetCellValue(array[m]); } if (l == 1) { sh.GetRow(l).GetCell(m).SetCellValue(array2[m]); } } } for (int n = 0; n < dataGridView2.RowCount; n++) { if (sh.GetRow(l) == null) { sh.CreateRow(l); } for (int num2 = 0; num2 < dataGridView2.ColumnCount; num2++) { if (sh.GetRow(l).GetCell(num2) == null) { sh.GetRow(l).CreateCell(num2); } if (dataGridView2[num2, n].Value != null) { sh.GetRow(l).GetCell(num2).SetCellValue(dataGridView2[num2, n].Value.ToString()); } } l++; } using (FileStream out2 = new FileStream(text, FileMode.Open, FileAccess.Write)) { wb.Write(out2); AutoClosingMessageBox.Show("匯出報表於" + text); } } else { AutoClosingMessageBox.Show(text + "檔案使用中,請確認檔案是在未開啟的狀態下"); } }
public JsonResult ImportExcel() { HttpPostedFileBase file = Request.Files["bookPageExcel"]; if (file == null) { return(Json(new { isSuccess = false, message = "请选择上传的Excel文件" })); } else { if (file.FileName.Split('.')[1] == "xls" || file.FileName.Split('.')[1] == "xlsx") { //对文件的格式判断,此处省略 List <FanHobbyInfo> fanHobbyInfos = new List <FanHobbyInfo>(); Stream inputStream = file.InputStream; HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputStream); NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 for (int i = (sheet.FirstRowNum + 2); i <= rowCount; i++) { IRow row = sheet.GetRow(i); FanHobbyInfo model = new FanHobbyInfo(); if (row != null) { model.SerialNumber = row.GetCell(0) != null?GetCellValue(row.GetCell(0)) : string.Empty; model.MainReadTypeName1 = row.GetCell(1) != null?GetCellValue(row.GetCell(1)) : string.Empty; model.MainReadTypeName2 = row.GetCell(2) != null?GetCellValue(row.GetCell(2)) : string.Empty; model.FoodTypeName = row.GetCell(3) != null?GetCellValue(row.GetCell(3)) : string.Empty; model.FruitsTypeName = row.GetCell(4) != null?GetCellValue(row.GetCell(4)) : string.Empty; model.SweetTypeName = row.GetCell(5) != null?GetCellValue(row.GetCell(5)) : string.Empty; model.WXNickName = row.GetCell(6) != null?GetCellValue(row.GetCell(6)) : string.Empty; model.WXId = row.GetCell(7) != null?GetCellValue(row.GetCell(7)) : string.Empty; model.Phone = row.GetCell(8) != null?GetCellValue(row.GetCell(8)) : string.Empty; model.Age = row.GetCell(9) != null?GetCellValue(row.GetCell(9)) : string.Empty; model.Profession = row.GetCell(10) != null?GetCellValue(row.GetCell(10)) : string.Empty; model.Education = row.GetCell(11) != null?GetCellValue(row.GetCell(11)) : string.Empty; //model.WXType = row.GetCell(12) != null ? GetCellValue(row.GetCell(12)) : string.Empty; model.Remark = row.GetCell(12) != null?GetCellValue(row.GetCell(12)) : string.Empty; model.CreateUser = row.GetCell(13) != null?GetCellValue(row.GetCell(13)) : string.Empty; model.CreateTime = row.GetCell(14) != null?GetCellValue(row.GetCell(14)) : string.Empty; model.UpdateTime = row.GetCell(15) != null?GetCellValue(row.GetCell(15)) : string.Empty; model.Device = row.GetCell(16) != null?GetCellValue(row.GetCell(16)) : string.Empty; model.System = row.GetCell(17) != null?GetCellValue(row.GetCell(17)) : string.Empty; model.Browser = row.GetCell(18) != null?GetCellValue(row.GetCell(18)) : string.Empty; model.IP = row.GetCell(19) != null?GetCellValue(row.GetCell(19)) : string.Empty; } fanHobbyInfos.Add(model); } var errorMsg = ""; var errorCount = 0; var conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (IDbConnection con = new SqlConnection(conStr)) { foreach (var book in fanHobbyInfos) { try { DynamicParameters dp = new DynamicParameters(); dp.Add("@SerialNumber", book.SerialNumber.Trim(), DbType.String); //dp.Add("@WXType", book.WXType.Trim(), DbType.String); dp.Add("@MainReadTypeName1", book.MainReadTypeName1.Trim(), DbType.String); dp.Add("@MainReadTypeName2", book.MainReadTypeName2.Trim(), DbType.String); dp.Add("@FoodTypeName", book.FoodTypeName.Trim(), DbType.String); dp.Add("@FruitsTypeName", book.FruitsTypeName.Trim(), DbType.String); dp.Add("@SweetTypeName", book.SweetTypeName.Trim(), DbType.String); dp.Add("@WXNickName", book.WXNickName.Trim(), DbType.String); dp.Add("@WXId", book.WXId.Trim(), DbType.String); dp.Add("@Phone", book.Phone.Trim(), DbType.String); dp.Add("@Age", book.Age.Trim(), DbType.String); dp.Add("@Profession", book.Profession.Trim(), DbType.String); dp.Add("@Education", book.Education.Trim(), DbType.String); dp.Add("@Remark", book.Remark.Trim(), DbType.String); dp.Add("@CreateUser", book.CreateUser.Trim(), DbType.String); dp.Add("@CreateTime", DateTime.Now, DbType.DateTime); dp.Add("@UpdateTime", book.UpdateTime != "" ? Convert.ToDateTime(book.UpdateTime.Trim()) : DateTime.Now, DbType.DateTime); dp.Add("@Device", book.Device.Trim(), DbType.String); dp.Add("@System", book.System.Trim(), DbType.String); dp.Add("@Browser", book.Browser.Trim(), DbType.String); dp.Add("@IP", book.IP.Trim(), DbType.String); dp.Add("@error", dbType: DbType.Int32, direction: ParameterDirection.Output, size: 1000); dp.Add("@errorMsg", dbType: DbType.String, direction: ParameterDirection.Output, size: 1000); con.Execute("BuildBookPage", dp, null, null, CommandType.StoredProcedure); if (!string.IsNullOrEmpty(dp.Get <string>("@errorMsg"))) { errorMsg += dp.Get <string>("@errorMsg") + "<br />"; } //errorCount = errorCount + dp.Get<int>("@error"); } catch (Exception e) { errorMsg = "导入失败"; errorCount++; } } } return(Json(new { isSuccess = (errorCount == 0), message = errorMsg })); } else { return(Json(new { isSuccess = false, message = "只能上传excel格式的文件" })); } } }
public EvaluationSheet GetSheet(int sheetIndex) { return(new HSSFEvaluationSheet((HSSFSheet)_uBook.GetSheetAt(sheetIndex))); }
public DataTable ExcelImport(string strFileName) { DataTable dt = new DataTable(); try { ISheet sheet = null; using (System.IO.MemoryStream memStream = new System.IO.MemoryStream()) { memStream.Position = 0; WebClient webClient = new WebClient(); var obj = webClient.DownloadData(strFileName);//fileName 是远程url地址,可以url直接下载 Stream stream = new MemoryStream(obj); if (strFileName.IndexOf("/raw") > 0) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(stream); sheet = xssfworkbook.GetSheetAt(0); } else if (strFileName.IndexOf(".xlsx") == -1) //2003 { HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream); sheet = hssfworkbook.GetSheetAt(0); } else//2007 { XSSFWorkbook xssfworkbook = new XSSFWorkbook(stream); sheet = xssfworkbook.GetSheetAt(0); } } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) //需求:列名的重复,则删除重复的列 { ICell cell = headerRow.GetCell(j); var name = cell.ToString(); name = Regex.Replace(name, "[ \\[ \\] \\^ \\*×(^)$%~!@#$…&%¥√+=<>《》!!???::•`·、。,;,.;\"‘’“”-]", " "); dt.Columns.Add(name); //添加这列名 } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.Blank: dataRow[j] = ""; break; case CellType.Boolean: dataRow[j] = row.GetCell(j); break; case CellType.Error: break; case CellType.Formula: dataRow[j] = row.GetCell(j).CellFormula; break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = row.GetCell(j).DateCellValue.ToString(); } else { dataRow[j] = row.GetCell(j); } break; case CellType.String: dataRow[j] = row.GetCell(j).StringCellValue; break; case CellType.Unknown: break; default: dataRow[j] = row.GetCell(j); break; } } } dt.Rows.Add(dataRow); } return(dt); } catch (Exception ex) { _logger.LogError("ExcelImport ex.Message:" + ex.Message + Environment.NewLine + "ex.StackTrace" + ex.StackTrace); return(dt); } }
/// <summary> /// 读取excel /// sheet.LastRowNum属性获取的是Excel中该工作表(sheet)的末行行号减1; /// headerRow.LastCellNum属性获取的是Excel中该行的列数 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <param name="sheetNum">工作表索引,以0开始</param> /// <param name="startRowNum">起始行号,即:表头在Excel中的行号</param> /// <param name="endRowNum">结束行号</param> /// <param name="startColNum">起始列号</param> /// <param name="endColNum">结束列号</param> /// <returns>DataTable</returns> public DataTable Import(string strFileName, int sheetNum, int startRowNum, int endRowNum, int startColNum, int endColNum) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } int sheetCount = hssfworkbook.NumberOfSheets; sheetNum = sheetNum < 0 || sheetNum > sheetCount - 1 ? 0 : sheetNum; HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetNum); HSSFRow headerRow = null; #region 行列号范围验证 startColNum = startColNum < 0 ? 0 : startColNum; startRowNum = startRowNum < 1 ? 1 : startRowNum; headerRow = (HSSFRow)sheet.GetRow(startRowNum - 1); endColNum = (endColNum > headerRow.LastCellNum || endColNum < 1) ? headerRow.LastCellNum : endColNum; endRowNum = (endRowNum - 1 > sheet.LastRowNum || endRowNum < 0) ? sheet.LastRowNum + 1 : endColNum; #endregion //添加列 for (int j = startColNum - 1; j < endColNum; j++) { HSSFCell cell = (HSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } //添加行 for (int i = startRowNum; i <= endRowNum - 1; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = startColNum - 1; j < endColNum; j++) { if (row.GetCell(j) != null) dataRow[j - startColNum + 1] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; }
/// <summary> /// 从Excel2003取数据并记录到List集合里 /// </summary> /// <param name="cellHeard">单元头的Key和Value:{ { "UserName", "姓名" }, { "Age", "年龄" } };</param> /// <param name="filePath">保存文件绝对路径</param> /// <param name="errorMsg">错误信息</param> /// <returns>转换好的List对象集合</returns> private static List <T> Excel2003ToEntityList <T>(Dictionary <string, string> cellHeard, string filePath, out StringBuilder errorMsg) where T : new() { errorMsg = new StringBuilder(); // 错误信息,Excel转换到实体对象时,会有格式的错误信息 List <T> enlist = new List <T>(); // 转换后的集合 List <string> keys = cellHeard.Keys.ToList(); // 要赋值的实体对象属性名称 try { using (FileStream fs = File.OpenRead(filePath)) { HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); // 获取此文件第一个Sheet页 for (int i = 1; i <= sheet.LastRowNum; i++) // 从1开始,第0行为单元头 { // 1.判断当前行是否空行,若空行就不在进行读取下一行操作,结束Excel读取操作 if (sheet.GetRow(i) == null) { break; } T en = new T(); string errStr = ""; // 当前行转换时,是否有错误信息,格式为:第1行数据转换异常:XXX列; for (int j = 0; j < keys.Count; j++) { // 2.若属性头的名称包含'.',就表示是子类里的属性,那么就要遍历子类,eg:UserEn.TrueName if (keys[j].IndexOf(".") >= 0) { // 2.1解析子类属性 string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries); string subClassName = properotyArray[0]; // '.'前面的为子类的名称 string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称 System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型 if (subClassInfo != null) { // 2.1.1 获取子类的实例 var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null); // 2.1.2 根据属性名称获取子类里的属性信息 System.Reflection.PropertyInfo properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName); if (properotyInfo != null) { try { // Excel单元格的值转换为对象属性的值,若类型不对,记录出错信息 properotyInfo.SetValue(subClassEn, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null); } catch (Exception e) { if (errStr.Length == 0) { errStr = "第" + i + "行数据转换异常:"; } errStr += cellHeard[keys[j]] + "列;"; } } } } else { // 3.给指定的属性赋值 System.Reflection.PropertyInfo properotyInfo = en.GetType().GetProperty(keys[j]); if (properotyInfo != null) { try { // Excel单元格的值转换为对象属性的值,若类型不对,记录出错信息 properotyInfo.SetValue(en, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null); } catch (Exception e) { if (errStr.Length == 0) { errStr = "第" + i + "行数据转换异常:"; } errStr += cellHeard[keys[j]] + "列;"; } } } } // 若有错误信息,就添加到错误信息里 if (errStr.Length > 0) { errorMsg.AppendLine(errStr); } enlist.Add(en); } } return(enlist); } catch (Exception ex) { throw ex; } }