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