public ResponseData UploadStudents(IFormFile formFile) { try { if (formFile != null && formFile.Length > 0) { Console.WriteLine("file name " + formFile.FileName); XSSFWorkbook excelUpload; using (var excelStream = formFile.OpenReadStream()) excelUpload = new XSSFWorkbook(excelStream); var programme = _programmeRepository.ReadDefaultProgramme(); if (programme.Id == 0) { return(ResponseData.SendFailMsg("No programme exist yet. Kindly create at least one")); } var sheet = excelUpload.GetSheet("Students"); for (var row = 1; row <= sheet.LastRowNum; row++) // iterating through rows { if (!CreateStudent(new Student { FirstName = sheet.GetRow(row).GetCell(0).StringCellValue, LastName = sheet.GetRow(row).GetCell(1).StringCellValue, Email = sheet.GetRow(row).GetCell(2).StringCellValue, MatricNumber = sheet.GetRow(row).GetCell(3).StringCellValue, ProgrammeId = programme.Id })) { return(ResponseData.SendFailMsg("Attempted to upload an Invalid student. Kindly retry with valid students")); } } return(ResponseData.SendSuccessMsg()); } return(ResponseData.SendFailMsg("Invalid upload file supplied")); } catch (Exception e) { Console.WriteLine(e); return(ResponseData.SendFailMsg("Error in uploading students. Kindly retry with valid students")); } }
/// <summary> /// 獲取Excel單元格內容 /// </summary> /// <param name="fileName">Excel文件,帶路徑</param> /// <param name="sheetName">Sheet Name,起始為0</param> /// <param name="iRow">行,起始為1</param> /// <param name="iCol">列,起始為1</param> /// <returns></returns> public static string GetExcelCellValueBySheetName(string fileName, string sheetName, int iRow, int iCol) { try { XSSFWorkbook xssfworkbook = new XSSFWorkbook(fileName); ISheet sheet = xssfworkbook.GetSheet(sheetName); string result = GetExcelCellValue(sheet, iRow, iCol); xssfworkbook.Close(); sheet = null; xssfworkbook = null; return(result); } catch (Exception ex) { return(""); } }
private void WriteLazada(string outputPath, string sourcePath) { XSSFWorkbook hssfwb; using (FileStream file = new FileStream(sourcePath, FileMode.Open, FileAccess.Read)) { hssfwb = new XSSFWorkbook(file); ISheet sheetRead = hssfwb.GetSheet("null1"); using (FileStream stream = new FileStream(outputPath, FileMode.CreateNew, FileAccess.Write)) { var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet("null1"); var writeRow = 0; for (int row = 1; row <= sheetRead.LastRowNum; row++) { if (sheetRead.GetRow(row) == null) { continue; } var sku = sheetRead.GetRow(row).GetCell(0).StringCellValue; var top100Sku = _top100Products.Select(s => s.SKU).ToList(); if (top100Sku.Contains(sku)) { var rowtemp = sheet.CreateRow(++writeRow); rowtemp.CreateCell(0).SetCellValue(sheetRead.GetRow(row).GetCell(0).StringCellValue); rowtemp.CreateCell(1).SetCellValue(sheetRead.GetRow(row).GetCell(1).StringCellValue); rowtemp.CreateCell(2).SetCellValue(sheetRead.GetRow(row).GetCell(2).StringCellValue); rowtemp.CreateCell(3).SetCellValue(sheetRead.GetRow(row).GetCell(3).StringCellValue); rowtemp.CreateCell(4).SetCellValue(sheetRead.GetRow(row).GetCell(4).StringCellValue); rowtemp.CreateCell(5).SetCellValue(sheetRead.GetRow(row).GetCell(5).StringCellValue); rowtemp.CreateCell(6).SetCellValue(sheetRead.GetRow(row).GetCell(6).StringCellValue); rowtemp.CreateCell(7).SetCellValue(sheetRead.GetRow(row).GetCell(7).StringCellValue); rowtemp.CreateCell(8).SetCellValue(sheetRead.GetRow(row).GetCell(8).StringCellValue); rowtemp.CreateCell(9).SetCellValue(sheetRead.GetRow(row).GetCell(9).StringCellValue); rowtemp.CreateCell(10).SetCellValue(sheetRead.GetRow(row).GetCell(10).StringCellValue); rowtemp.CreateCell(11).SetCellValue(sheetRead.GetRow(row).GetCell(11).StringCellValue); } } workbook.Write(stream); } } }
public static void ReadYinTaiStoreExcel() { Stream excel = new FileStream(@"E:\工作资料\门店帐号信息完整版.xlsx", FileMode.Open); IWorkbook workbook = new XSSFWorkbook(excel); ISheet sheet = workbook.GetSheet("门店信息"); var sb = new StringBuilder(); for (int i = sheet.FirstRowNum + 1; i < sheet.LastRowNum; i++) { var row = sheet.GetRow(i); int j = row.FirstCellNum + 3; sb.AppendFormat( "INSERT INTO [YintaiEvent].[dbo].[Store]([StoreId],[City],[StoreName],[CompanyFullName],[Address],[OfficeAddress],[Phone],[Bank1],[BankAccount1],[Bank2],[BankAccount2],[InUserId],[EditUserId],[InDate],[EditDate])VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',{13},{14})", i, row.GetCell(j - 2), row.GetCell(j - 1), row.GetCell(j), row.GetCell(j + 1), row.GetCell(j + 2), row.GetCell(j + 3), row.GetCell(j + 4), row.GetCell(j + 5), row.GetCell(j + 6), row.GetCell(j + 7), "0", "0", "GetDate()", "GetDate()"); sb.Append("\n"); } var str = sb.ToString(); }
/// <summary> /// 更新Excel表格 /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="updateData">需更新的数据</param> /// <param name="coluids">需更新的列号</param> /// <param name="rowid">需更新的开始行号</param> public static bool UpdateExcel(string outputFile, string sheetname, DataTable updateData, int coluids, int rowid) { try { if (!File.Exists(outputFile)) { return(false); } FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); XSSFWorkbook hssfworkbook = new XSSFWorkbook(readfile); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int i = 0; i < updateData.Rows.Count; i++) { for (int j = 0; j < updateData.Columns.Count; j++) { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(j + coluids) == null) { sheet1.GetRow(i + rowid).CreateCell(j + coluids); } sheet1.GetRow(i + rowid).GetCell(j + coluids).SetCellValue(updateData.Rows[i][j].ToString()); } } try { readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); } return(true); } catch (Exception ex) { return(false); } }
public static async Task GetNumCases() { WebClient webClient = new WebClient(); Uri uri = new Uri("https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx"); byte[] v = await webClient.DownloadDataTaskAsync(uri); ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(v); IWorkbook workbook = new XSSFWorkbook(byteArrayInputStream); ISheet sheet1 = workbook.GetSheet("UTLAs"); IRow headerRow = sheet1.GetRow(7); if (headerRow.GetCell(0).StringCellValue != "Area Code") { throw new FormatException("Table not in the expected format"); } }
private MemoryStream SetDataToExcel(Action<ISheet> actionMethod) { //Load template file FileStream file = new FileStream(templdateName, FileMode.Open, FileAccess.Read); XSSFWorkbook workbook = new XSSFWorkbook(file); ISheet sheet = workbook.GetSheet(SheetName); if (actionMethod != null) actionMethod(sheet); sheet.ForceFormulaRecalculation = true; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); //ms.Position = 0; return ms; } }
public ISheet GetSheetObject() { IWorkbook book; ISheet sheet = null; FileStream fs = new FileStream(ExlFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); try { book = new XSSFWorkbook(fs); //book = new HSSFWorkbook(fs); sheet = book.GetSheet(sheetName); //.CreateSheet("Sheet1"); } catch (Exception ex) { Console.WriteLine("undefined sheet name: " + ex.Message); } return(sheet); }
public void TestMergingOverlappingCols_OVERLAPS_2_MINOR() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test"); CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0); CT_Col col = cols.AddNewCol(); col.min = (2 + 1); col.max = (4 + 1); col.width = (20); col.customWidth = (true); sheet.GroupColumn((short)1, (short)3); cols = sheet.GetCTWorksheet().GetColsArray(0); //logger.log(POILogger.DEBUG, "testMergingOverlappingCols_OVERLAPS_2_MINOR/cols:" + cols); Assert.AreEqual(1, cols.GetColArray(0).outlineLevel); Assert.AreEqual(2, cols.GetColArray(0).min); // 1 based Assert.AreEqual(2, cols.GetColArray(0).max); // 1 based Assert.AreEqual(false, cols.GetColArray(0).customWidth); Assert.AreEqual(1, cols.GetColArray(1).outlineLevel); Assert.AreEqual(3, cols.GetColArray(1).min); // 1 based Assert.AreEqual(4, cols.GetColArray(1).max); // 1 based Assert.AreEqual(true, cols.GetColArray(1).customWidth); Assert.AreEqual(0, cols.GetColArray(2).outlineLevel); Assert.AreEqual(5, cols.GetColArray(2).min); // 1 based Assert.AreEqual(5, cols.GetColArray(2).max); // 1 based Assert.AreEqual(true, cols.GetColArray(2).customWidth); Assert.AreEqual(3, cols.sizeOfColArray()); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testMergingOverlappingCols_OVERLAPS_2_MINOR"); sheet = (XSSFSheet)wb.GetSheet("test"); for (int i = 2; i <= 4; i++) { Assert.AreEqual(20 * 256, sheet.GetColumnWidth(i), "Unexpected width of column " + i); } Assert.AreEqual(sheet.DefaultColumnWidth * 256, sheet.GetColumnWidth(1), "Unexpected width of column " + 1); }
/// <summary> /// 从上传的Excel读出数据 /// </summary> private async Task <List <DemandDetailListDto> > GetDemandDetailDataAsync() { string fileName = _hostingEnvironment.WebRootPath + "/upload/files/DemandDetailUpload.xlsx"; var demandList = new List <DemandDetailListDto>(); using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheet("DemandDetailData"); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } if (sheet != null) { //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = 1; i <= rowCount; ++i)//排除首行标题 { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } var demandData = new DemandDetailListDto(); if (row.GetCell(0) != null) { demandData.RetailerCode = row.GetCell(0).ToString(); demandData.RetailerName = row.GetCell(1).ToString(); demandData.Name = row.GetCell(2).ToString(); demandData.SuggestPrice = decimal.Parse(row.GetCell(3).ToString()); demandData.WholesalePrice = decimal.Parse(row.GetCell(4).ToString()); demandData.YearOnYear = decimal.Parse(row.GetCell(5).ToString()); demandData.LastMonthNum = int.Parse(row.GetCell(6).ToString()); //demandData.IsAlien = Boolean.Parse(row.GetCell(7).ToString()); //demandData.Type = int.Parse(row.GetCell(8).ToString()); demandList.Add(demandData); } } } return(await Task.FromResult(demandList)); } }