private void ButtonExportExecute()
{
_trashShippeds.AddRange(TrashModule.GetTrashShippedQuantitySum(DatePickerBegin, DatePickerEnd));
#region OleDB
//string databaseDirectory = AppSettingConfig.DbfFilePath();
//string sql4 = "SELECT invosub.IN_DATE,invosub.I_01,item.I_03,SUM(invosub.QUANTITY) FROM INVOSUB.dbf invosub " +
// "INNER JOIN ITEM.dbf item ON invosub.I_01 = item.I_01 AND invosub.F_01 = item.F_01 " +
// "WHERE invosub.IN_DATE Between cDate('" + DatePickerBegin.ToString() + "') and cDate('" + DatePickerEnd.ToString() + "') " +
// "GROUP BY invosub.IN_DATE,invosub.I_01,item.I_03 " +
// "ORDER BY invosub.IN_DATE,invosub.I_01";
//DataTable dt4 = GetOleDbDbfDataTable(databaseDirectory, sql4);
//DataGridInCash_Copy3.ItemsSource = dt4.DefaultView;
#endregion
var originalSources = new List <OriginalSource>();
foreach (var shipped in _trashShippeds)
{
originalSources.Add(new OriginalSource
{
DateTime = shipped.IN_DATE,
TextileNo = shipped.I_01,
TextileColorName = shipped.I_03,
Weight = shipped.Quantity
});
}
var filterText = FilterText;
var filterOriginalSources = string.IsNullOrEmpty(filterText) == true?originalSources.Select(s => s.TextileNo).Distinct() : originalSources.Where(w => w.TextileColorName.Contains(filterText)).Select(s => s.TextileNo).Distinct();;
var everyDate = originalSources.Select(s => s.DateTime).Distinct();
var everyTextileNo = filterOriginalSources;
var textileDatas = new List <TextileData>();
foreach (var date in everyDate)
{
textileDatas.Add(new TextileData
{
DateTime = date,
TextileColors = originalSources.Where(w => w.DateTime == date).Select(s => new TextileColor {
TextileNo = s.TextileNo, Weight = s.Weight, TextileColorName = s.TextileColorName
}).ToList()
});
}
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Report");
XSSFRow row = (XSSFRow)ws.CreateRow(0);
var textileNameNumber = 1;
ExcelHelper.CreateCell(row, 0, "時間", null);
var priviousWeightList = new Dictionary <string, double>();
foreach (var item in everyTextileNo)
{
ExcelHelper.CreateCell(row, textileNameNumber, originalSources.Where(w => w.TextileNo == item).First().TextileColorName, null);
priviousWeightList.Add(item, 0);
textileNameNumber++;
}
var rowNumber = 1;
var priviousYear = textileDatas.First().DateTime.ToString("yyyy");
foreach (var textiles in textileDatas)
{
var columnNumber = 1;
XSSFRow rowTextile = (XSSFRow)ws.CreateRow(rowNumber);
ExcelHelper.CreateCell(rowTextile, 0, textiles.DateTime.ToString("yyyy/MM/dd"), null);
if (textiles.DateTime.ToString("yyyy") != priviousYear)
{
priviousYear = textiles.DateTime.ToString("yyyy");
foreach (var item in everyTextileNo)
{
priviousWeightList[item] = 0;
}
}
foreach (var textileNo in everyTextileNo)
{
var textileData = textiles.TextileColors.Where(w => w.TextileNo == textileNo);
var weight = textileData.Count() == 0 ? priviousWeightList[textileNo] : textileData.First().Weight + priviousWeightList[textileNo];
ExcelHelper.CreateCell(rowTextile, columnNumber, weight.ToString(), null);
priviousWeightList[textileNo] = weight;
columnNumber++;
}
rowNumber++;
}
FileStream file = new FileStream(string.Concat(AppSettingConfig.ReportsFilePath(), @"\", filterText, "報表", DatePickerBegin.ToString("yyyyMMdd"), "-", DatePickerEnd.ToString("yyyyMMdd"), ".xlsx"), FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}