public void CreateExcelFile(IWorkbook wb, ExcelContent excelContent)
{
foreach (var excelSheet in excelContent.ExcelSheetContents)
{
ISheet ws = wb.CreateSheet(excelSheet.SheetName);
ws.SetMargin(MarginType.LeftMargin, excelSheet.LeftMargin);
ws.SetMargin(MarginType.RightMargin, excelSheet.RightMargin);
ws.SetMargin(MarginType.TopMargin, excelSheet.TopMargin);
ws.SetMargin(MarginType.BottomMargin, excelSheet.BottomMargin);
XSSFRow row = (XSSFRow)ws.CreateRow(0);
row.Height = excelSheet.ColumnHeight;
foreach (ExcelColumnContent columnContent in excelSheet.ExcelColumnContents)
{
if (columnContent.Width != 0)
{
ws.SetColumnWidth(excelSheet.ExcelColumnContents.ToList().IndexOf(columnContent), columnContent.Width);
}
CreateCell(row, excelSheet.ExcelColumnContents.ToList().IndexOf(columnContent), columnContent.CellValue, columnContent.CellStyle);
}
int rowIndex = 1;
foreach (ExcelRowContent rowContent in excelSheet.ExcelRowContents)
{
XSSFRow rowTextile = (XSSFRow)ws.CreateRow(rowIndex);
rowTextile.Height = rowContent.Height;
for (int cellIndex = 0; cellIndex < rowContent.ExcelCellContents?.Count; cellIndex++)
{
if (double.TryParse(rowContent.ExcelCellContents.ElementAt(cellIndex).CellValue, out double cellValue))
{
CreateCell(rowTextile, cellIndex, cellValue, rowContent.ExcelCellContents.ElementAt(cellIndex).CellStyle);
}
else
{
CreateCell(rowTextile, cellIndex, rowContent.ExcelCellContents.ElementAt(cellIndex).CellValue, rowContent.ExcelCellContents.ElementAt(cellIndex).CellStyle);
}
if (rowContent.ExcelCellContents.ElementAt(cellIndex).CellRangeAddress != null)
{
ws.AddMergedRegion(rowContent.ExcelCellContents.ElementAt(cellIndex).CellRangeAddress);
}
}
rowIndex++;
}
}
FileStream file = new FileStream(string.Concat(AppSettingConfig.FilePath(), @"\", excelContent.FileName, ".xlsx"), FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
public List <ExcelContent> ParseExcel()
{
ISheet ws = _workBook.GetSheetAt(0);
List <ExcelContent> ExcelContent = new List <ExcelContent>();
//iteracja przez wiersze
for (int i = 0; i < ws.LastRowNum; i++)
{
IRow row = ws.GetRow(i);
//czy wiersz jest pusty
if (row == null || IsRowEmpty(row))
{
continue;
}
//jeśli wiersz ma dane to parsuj dane
if (IsDataRow(row))
{
//Dane z wiersza zapisywane są do obiektu
ExcelContent content = new ExcelContent()
{
Id = Guid.NewGuid(),
BarCode = row.Cells[3].NumericCellValue,
ProductName = row.Cells[8].StringCellValue,
NumberOfItems = double.Parse(row.Cells[24].ToString()),
ItemGrosValue = double.Parse(row.Cells[27].ToString()),
TotalGrosValue = double.Parse(row.Cells[35].ToString())
};
ExcelContent.Add(content);
}
}
return(ExcelContent);
}
public RevenueExportViewModel(string revenueDate)//2021-5
{
DateTime date = DateTime.Parse(revenueDate);
List <TrashCustomerShipped> invoSubList = new List <TrashCustomerShipped>(TrashModule.GetInvoSub(date));
List <CustomerRevenue> customerRevenue = invoSubList.GroupBy(g => new
{
cusotmerID = g.C_01,
customerName = g.C_Name
}).Select(s => new CustomerRevenue
{
CustomerID = s.Key.cusotmerID,
CustomerName = s.Key.customerName,
Revenue = s.Sum(sum => Math.Ceiling(sum.Price * sum.Quantity))
}).OrderBy(o => o.CustomerID).ToList();
customerRevenue.Add(new CustomerRevenue
{
CustomerName = "總金額",
Revenue = customerRevenue.Sum(s => s.Revenue)
});
List <ExcelRowContent> excelRowContents = new List <ExcelRowContent>();
foreach (var item in customerRevenue)
{
ExcelRowContent excelRowContent = new ExcelRowContent
{
ExcelCellContents = new List <ExcelCellContent>()
{
new ExcelCellContent {
CellValue = item.CustomerName
},
new ExcelCellContent {
CellValue = item.Revenue.ToString()
}
}
};
excelRowContents.Add(excelRowContent);
}
;
var excelHelper = new ExcelHelper();
ExcelContent excelContent = new ExcelContent
{
FileName = string.Concat("營收表", date.ToString("yyyy-MM")),
ExcelSheetContents = new List <ExcelSheetContent>
{
new ExcelSheetContent
{
SheetName = "營業額",
ExcelColumnContents = new List <ExcelColumnContent>
{
new ExcelColumnContent
{
CellValue = "客戶名稱",
Width = 3000
},
new ExcelColumnContent
{
CellValue = "營業額",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "實收金額",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "差額",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "0.99",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "單價折",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "故障折",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "尾數折",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "稅金發票1",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "稅金發票2",
Width = 5000
},
new ExcelColumnContent
{
CellValue = "稅金發票3",
Width = 5000
}
},
ExcelRowContents = excelRowContents
},
new ExcelSheetContent
{
SheetName = "人事開銷",
ExcelColumnContents = new List <ExcelColumnContent>(),
ExcelRowContents = new List <ExcelRowContent>()
},
new ExcelSheetContent
{
SheetName = "基本開銷",
ExcelColumnContents = new List <ExcelColumnContent>(),
ExcelRowContents = new List <ExcelRowContent>()
},
new ExcelSheetContent
{
SheetName = "紗商",
ExcelColumnContents = new List <ExcelColumnContent>(),
ExcelRowContents = new List <ExcelRowContent>()
},
new ExcelSheetContent
{
SheetName = "織廠",
ExcelColumnContents = new List <ExcelColumnContent>(),
ExcelRowContents = new List <ExcelRowContent>()
},
new ExcelSheetContent
{
SheetName = "加工廠",
ExcelColumnContents = new List <ExcelColumnContent>(),
ExcelRowContents = new List <ExcelRowContent>()
},
new ExcelSheetContent
{
SheetName = "其他開銷",
ExcelColumnContents = new List <ExcelColumnContent>(),
ExcelRowContents = new List <ExcelRowContent>()
}
}
};
IWorkbook wb = new XSSFWorkbook();
excelHelper.CreateExcelFile(wb, excelContent);
}
private void ButtonExportCustomerExecute()
{
IEnumerable <TrashCustomerShipped> trashCustomerShippedList = TrashModule.GetCustomerShippedList(CustomerName, CustomerDatePickerBegin, CustomerDatePickerEnd)
.GroupBy(g => new { g.C_Name, g.I_03 })
.Select(s => new TrashCustomerShipped {
C_Name = s.Key.C_Name, I_03 = s.Key.I_03, Quantity = s.Sum(item => item.Quantity)
});
IEnumerable <TrashCustomerShipped> trashCustomerShippeds = string.IsNullOrEmpty(TextileName) ? trashCustomerShippedList.OrderBy(o => o.I_03) : trashCustomerShippedList.Where(w => w.I_03.Contains(TextileName)).OrderBy(o => o.I_03);
IOrderedEnumerable <IGrouping <string, TrashCustomerShipped> > groupCustomerShippeds = trashCustomerShippeds
.OrderByDescending(o => o.Quantity)
.GroupBy(g => g.C_Name)
.OrderByDescending(o => o.Select(s => s.Quantity).Sum());
ExcelContent excelContent = new ExcelContent
{
FileName = string.Concat(TextileName + "客戶出貨紀錄", DateTime.Now.ToString("yyyyMMdd")),
ExcelSheetContents = new List <ExcelSheetContent>(),
};
List <ExcelSheetContent> excelSheetContents = new List <ExcelSheetContent>();
foreach (IGrouping <string, TrashCustomerShipped> customerShippeds in groupCustomerShippeds)
{
ExcelSheetContent excelSheetContent = new ExcelSheetContent();
excelSheetContent.SheetName = customerShippeds.Key;
excelSheetContent.ExcelColumnContents = new List <ExcelColumnContent>()
{
new ExcelColumnContent()
{
CellValue = "客戶名稱",
Width = 3000
},
new ExcelColumnContent()
{
CellValue = "布種名稱",
Width = 5200
},
new ExcelColumnContent()
{
CellValue = "重量",
Width = 3000
}
};
excelSheetContent.ExcelRowContents = new List <ExcelRowContent>();
foreach (TrashCustomerShipped customerShipped in customerShippeds)
{
excelSheetContent.ExcelRowContents.Add(new ExcelRowContent()
{
ExcelCellContents = new List <ExcelCellContent>()
{
new ExcelCellContent()
{
CellValue = customerShipped.C_Name
},
new ExcelCellContent()
{
CellValue = customerShipped.I_03
},
new ExcelCellContent()
{
CellValue = customerShipped.Quantity.ToString()
}
}
});
}
excelSheetContents.Add(excelSheetContent);
}
var customerTotals = groupCustomerShippeds.Select(s => new { customerName = s.Key, totalQuantity = s.Sum(sum => sum.Quantity) });
ExcelSheetContent excelSheetContentTotal = new ExcelSheetContent()
{
SheetName = "客戶排名",
ExcelColumnContents = new List <ExcelColumnContent>()
{
new ExcelColumnContent()
{
CellValue = "客戶名稱",
Width = 3000
},
new ExcelColumnContent()
{
CellValue = "總出貨數",
Width = 3000
}
},
ExcelRowContents = new List <ExcelRowContent>()
};
foreach (var customerTotal in customerTotals)
{
List <ExcelRowContent> excelRowContent = new List <ExcelRowContent>()
{
new ExcelRowContent()
{
ExcelCellContents = new List <ExcelCellContent>()
{
new ExcelCellContent()
{
CellValue = customerTotal.customerName
},
new ExcelCellContent()
{
CellValue = customerTotal.totalQuantity.ToString()
}
}
}
};
excelSheetContentTotal.ExcelRowContents.AddRange(excelRowContent);
}
List <ExcelSheetContent> excelSheetContentTotals = new List <ExcelSheetContent>();
excelSheetContentTotals.Add(excelSheetContentTotal);
excelContent.ExcelSheetContents.AddRange(excelSheetContentTotals);
excelContent.ExcelSheetContents.AddRange(excelSheetContents);
ExcelHelper excelHelper = new ExcelHelper();
IWorkbook wb = new XSSFWorkbook();
excelHelper.CreateExcelFile(wb, excelContent);
}
private void ExportShippingCheckExecute()
{
List <StoreSearchData <StoreSearchColorDetail> > excelDailyShippedList = ExcelModule.GetExcelDailyShippedList(ShippingCheckDate);
IEnumerable <TrashShipped> trashShipped = TrashModule.GetTrashShippedQuantitySum(ShippingCheckDate, ShippingCheckDate);
ExternalDataHelper externalDataHelper = new ExternalDataHelper();
IEnumerable <TextileNameMapping> textileNameMappings = externalDataHelper.GetTextileNameMappings();
List <OriginalSource> trashItems = new List <OriginalSource>();
foreach (TrashShipped shipped in trashShipped)
{
trashItems.Add(new OriginalSource
{
DateTime = shipped.IN_DATE,
TextileNo = shipped.I_01,
TextileColorName = shipped.I_03,
Weight = shipped.Quantity
});
}
List <Container> trashPrimary = new List <Container>();
foreach (OriginalSource trashItem in trashItems)
{
int priviousDistance = 10;
string textileName = string.Empty;
string textileColor = string.Empty;
int shippedCount = 0;
foreach (StoreSearchData <StoreSearchColorDetail> excelDailyShippedItem in excelDailyShippedList)
{
TextileNameMapping textileNameMapping = textileNameMappings.ToList().Find(f => f.Inventory.Contains(excelDailyShippedItem.TextileName)) ?? new TextileNameMapping();
foreach (StoreSearchColorDetail colorDetail in excelDailyShippedItem.StoreSearchColorDetails)
{
string accountMapping = textileNameMapping.Account == null ? string.Empty : textileNameMapping.Account.FirstOrDefault();
if (trashItem.TextileColorName == string.Concat(accountMapping.Split('*')[0], colorDetail.ColorName.Split('-')[0]))
{
textileColor = colorDetail.ColorName;
textileName = excelDailyShippedItem.TextileName;
priviousDistance = 0;
shippedCount = colorDetail.ShippedCount;
break;
}
}
}
trashPrimary.Add(new Container()
{
OriginalSource = trashItem,
TextileName = textileName,
ColorName = textileColor,
ShippedCount = shippedCount,
Distance = priviousDistance
});
}
List <Container> excelPrimary = new List <Container>();
foreach (StoreSearchData <StoreSearchColorDetail> excelDailyShippedItem in excelDailyShippedList)
{
var priviousDistance = 10;
var textileName = string.Empty;
var textileColor = string.Empty;
foreach (var colorDetail in excelDailyShippedItem.StoreSearchColorDetails)
{
TextileNameMapping textileNameMapping = textileNameMappings.ToList().Find(f => f.Inventory.Contains(excelDailyShippedItem.TextileName)) ?? new TextileNameMapping();
OriginalSource originalSource = new OriginalSource();
foreach (var trashItem in trashItems)
{
string accountMapping = textileNameMapping.Account == null ? string.Empty : textileNameMapping.Account.FirstOrDefault();
if (trashItem.TextileColorName == string.Concat(accountMapping.Split('*')[0], colorDetail.ColorName.Split('-')[0]))
{
originalSource.DateTime = trashItem.DateTime;
originalSource.TextileColorName = trashItem.TextileColorName;
originalSource.Weight = trashItem.Weight;
originalSource.TextileNo = trashItem.TextileNo;
break;
}
}
excelPrimary.Add(new Container()
{
OriginalSource = originalSource,
TextileName = excelDailyShippedItem.TextileName,
ColorName = colorDetail.ColorName,
ShippedCount = colorDetail.ShippedCount,
Distance = priviousDistance
});
}
}
ExcelHelper excelHelper = new ExcelHelper();
IWorkbook wb = new XSSFWorkbook();
ICellStyle positionStyle = wb.CreateCellStyle();
positionStyle.WrapText = true;
positionStyle.Alignment = HorizontalAlignment.Center;
positionStyle.VerticalAlignment = VerticalAlignment.Center;
ICellStyle estyle = wb.CreateCellStyle();
estyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
estyle.FillPattern = FillPattern.SolidForeground;
ICellStyle a2style = wb.CreateCellStyle();
a2style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Coral.Index;
a2style.FillPattern = FillPattern.SolidForeground;
List <ExcelRowContent> trashPrimaryExcelRowContent = new List <ExcelRowContent>();
foreach (var item in trashPrimary.OrderByDescending(t => t.OriginalSource.TextileColorName == null).ThenBy(t => t.TextileName).ThenBy(o => o.ColorName))
{
var approximateNumber = item.OriginalSource.Weight / 20;
var round = Math.Round(approximateNumber, 0, MidpointRounding.AwayFromZero);
var isEqual = round == item.ShippedCount;
ExcelRowContent excelCellContents = new ExcelRowContent
{
ExcelCellContents = new List <ExcelCellContent>
{
new ExcelCellContent {
CellValue = item.OriginalSource.TextileColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.OriginalSource.Weight.ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = (approximateNumber).ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.TextileName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ShippedCount.ToString(), CellStyle = isEqual ? positionStyle : estyle
},
}
};
trashPrimaryExcelRowContent.Add(excelCellContents);
}
;
List <ExcelRowContent> excelPrimaryExcelRowContent = new List <ExcelRowContent>();
foreach (var item in excelPrimary.OrderByDescending(t => t.OriginalSource.TextileColorName == null).ThenBy(t => t.TextileName).ThenBy(o => o.ColorName))
{
var approximateNumber = item.OriginalSource.Weight / 20;
var round = Math.Round(approximateNumber, 0, MidpointRounding.AwayFromZero);
var isEqual = round == item.ShippedCount;
ExcelRowContent excelCellContents = new ExcelRowContent
{
ExcelCellContents = new List <ExcelCellContent>
{
new ExcelCellContent {
CellValue = item.OriginalSource.TextileColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.OriginalSource.Weight.ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = (approximateNumber).ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.TextileName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ShippedCount.ToString(), CellStyle = isEqual ? positionStyle : estyle
}
}
};
excelPrimaryExcelRowContent.Add(excelCellContents);
}
;
ExcelContent excelContent = new ExcelContent
{
FileName = string.Concat("庫存對照清單", ShippingCheckDate.ToString("yyyy-MM-dd")),
ExcelSheetContents = new List <ExcelSheetContent>
{
new ExcelSheetContent
{
SheetName = "Super為主",
ExcelColumnContents = new List <ExcelColumnContent>
{
new ExcelColumnContent
{
CellValue = "Super布種名稱顏色",
Width = 6450
},
new ExcelColumnContent
{
CellValue = "出貨重量",
Width = 2800
},
new ExcelColumnContent
{
CellValue = "約略出貨數",
Width = 2000
},
new ExcelColumnContent
{
CellValue = "布種名稱",
Width = 4550
},
new ExcelColumnContent
{
CellValue = "顏色",
Width = 5550
},
new ExcelColumnContent
{
CellValue = "出貨數量",
Width = 1850
}
},
ExcelRowContents = trashPrimaryExcelRowContent
},
new ExcelSheetContent
{
SheetName = "Excel為主",
ExcelColumnContents = new List <ExcelColumnContent>
{
new ExcelColumnContent
{
CellValue = "Super布種名稱顏色",
Width = 6450
},
new ExcelColumnContent
{
CellValue = "出貨重量",
Width = 2800
},
new ExcelColumnContent
{
CellValue = "約略出貨數",
Width = 2000
},
new ExcelColumnContent
{
CellValue = "布種名稱",
Width = 4550
},
new ExcelColumnContent
{
CellValue = "顏色",
Width = 5550
},
new ExcelColumnContent
{
CellValue = "出貨數量",
Width = 1850
}
},
ExcelRowContents = excelPrimaryExcelRowContent
}
}
};
excelHelper.CreateExcelFile(wb, excelContent);
}
