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