상속: OfficeDocuments
예제 #1
0
    public DataTable[] GetDataTablesFromExcelStream(Stream ExcelStream)
    {
        Aspose.Excel.License license = new Aspose.Excel.License();
        license.SetLicense("Aspose.Excel.lic");

        try
        {
            Excel excel = new Excel();
            excel.Open(ExcelStream);
            Worksheets sheets = excel.Worksheets;
            DataTable[] tables = new DataTable[sheets.Count];
            int index = 0;
            foreach (Worksheet sheet in sheets)
            {
                Cells cells = sheet.Cells;
                tables[index] = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);
                tables[index++].TableName = sheet.Name;
            }
            return tables;
        }
        catch
        {
            return null;
        }
    }
예제 #2
0
        /// <summary>
        /// Returns an active instance of Excel or a new one.
        /// </summary>
        /// <returns></returns>
        public static IExcel GetInstance() {

            //Set a compatible culture
            Thread.CurrentThread.CurrentCulture = CULTURE_US;

            //Get the running instance of Excel or create one if there isn't.
            bool isCreatedApp = false;
            Object app = null;
            Guid excelguid = typeof(Excel).GUID;
            NativeMethods.GetActiveObject(ref excelguid, IntPtr.Zero, out app);
            if (app == null) {
                try {
                    app = new Excel();
                    isCreatedApp = true;
                } catch {
                    throw new ExcelError("Failed to launch Excel.");
                }
            }

            //Cast to interface
            IExcel excel = app as IExcel;
            if (excel == null)
                throw new ExcelError("Failed to interface with Excel.");

            //Adds a worksheet if it's an empty instance of Excel
            if (isCreatedApp) {
                IWorkbook workbook = excel.Workbooks.Add();
                workbook.Worksheets.Add();
                excel.Visible = true;
            }

            return excel;
        }
예제 #3
0
파일: ExcelCov.cs 프로젝트: Centny/cswf.doc
 public static Excel Dequeue(string src)
 {
     Excel app;
     if (Cached.TryDequeue(out app))
     {
         try
         {
             app.Book = app.App.Workbooks.Open(src, 0, true, 5, "", "",
                 true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
         }
         catch (Exception e)
         {
             Cached.Enqueue(app);
             throw e;
         }
         return app;
     }
     try
     {
         //ProcKiller.Shared.Lock();
         app = new Excel(new Application());
         app.App.Visible = true;
         app.Book = app.App.Workbooks.Open(src, 0, true, 5, "", "",
             true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
         app.Pid = CovProc.GetWindowThreadProcessId(app.App.ActiveWindow.Hwnd);
         //ProcKiller.AddRunning(app.Pid);
         //ProcKiller.Shared.Unlock();
     }
     catch (Exception e)
     {
         //ProcKiller.Shared.Unlock();
         throw e;
     }
     return app;
 }
예제 #4
0
        /// <summary>
        /// EXCEL画线
        /// </summary>
        /// <param name="excel"></param>
        public static void DrawExcelBorders(Excel.Application excel, object cell1, object cell2)
        {
            Excel.Range range = excel.get_Range(cell1, cell2);
            //range.Select();
            Excel.Borders borders = range.Borders;
            borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;

            borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
            borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
            borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
            borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
            borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
        }
예제 #5
0
 public void checkin(Excel.Workbook activeWorkbook)
 {
     try
     {
         if (MessageBox.Show(resources.GetString("sure_check_in"), resources.GetString("checkin"), MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK)
         {
             object saveChanges = true;
             object missing = Type.Missing;
             String localFileName = activeWorkbook.FullName;
             activeWorkbook.Close(saveChanges, missing, missing); // Always we save document
             docXML.refresh(); // Refresh document list
             if (docXML.isOpenKMDocument(localFileName))
             {
                 OKMDocument oKMDocument = docXML.getOpenKMDocument(localFileName);
                 docXML.remove(oKMDocument);
                 DocumentLogic.checkin(oKMDocument, configXML.getHost(), configXML.getUser(), configXML.getPassword());
                 if (File.Exists(localFileName))
                 {
                     File.Delete(localFileName);
                 }
             }
         }
     }
     catch (Exception e)
     {
         String errorMsg = "OpenKMExcelAddIn - (checkinButton_Click)\n" + e.Message + "\n\n" + e.StackTrace;
         MessageBox.Show(errorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
     }
 }
예제 #6
0
 private static void AddExcelDocument(string[] attributes)
 {
     Dictionary<string, string> attr = getDictAttr(attributes);
     string documentName = returnNameCmdValue(attr);
     if (documentName != null)
     {
         Excel newDoc = new Excel(documentName);
         documents.Add(newDoc);
         Console.WriteLine("Document added: " + documentName);
         foreach (var key in attr.Keys)
         {
             switch (key)
             {
                 case "rows":
                     newDoc.Rows = attr[key];
                     break;
                 case "cols":
                     newDoc.Cols = attr[key];
                     break;
                 case "version":
                     newDoc.Version = attr[key];
                     break;
                 case "size":
                     newDoc.SizeInBytes = attr[key];
                     break;
                 case "content":
                     newDoc.Content = attr[key];
                     break;
             }
         }
     }
 }
예제 #7
0
 public static Excel LoadExcel(string path)
 {
     FileInfo file = new FileInfo(path);
     ExcelPackage ep = new ExcelPackage(file);
     Excel xls = new Excel(ep.Workbook);
     return xls;
 }
        public byte[] GetExcel(Grid grid, string sheetName, string sidx, string sord, string filters)
        {
            Excel excel = new Excel(grid);
            System.Data.DataTable dt = excel.SQL.GetDataTable(sidx, sord, filters);

            return excel.GetFile(sheetName, sidx, sord, filters);
        }
예제 #9
0
 public static Excel CreateExcel(string path)
 {
     ExcelPackage ep = new ExcelPackage ();
     ep.Workbook.Worksheets.Add ("sheet");
     Excel xls = new Excel(ep.Workbook);
     SaveExcel (xls, path);
     return xls;
 }
예제 #10
0
 public void BasicBlue()
 {
     var excel = new Excel(new BlueStyle());
     excel.CreateSheet("Test");
     excel.WriteObject(Reports, 0, 0);
     excel.SetColumnWidth(0, 0, new[] { 5, 35 });
     excel.WriteFile(Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, "aa.xlsx"));
 }
예제 #11
0
    static void test()
    {
        Excel xls = new Excel();
        ExcelTable table = new ExcelTable();
        table.TableName = "1";
        xls.Tables.Add(table);

        ExcelHelper.SaveExcel(xls, "/Users/junfeixi/Desktop/test111.xlsx");
    }
예제 #12
0
        public void ExportExcel(DataSet dataSet)
        {
            Excel excel = new Excel();
            Workbook workbook = excel.Workbooks.Add();

            for (int i = 0; i < dataSet.Tables.Count; i++)
            {
                DataTable dataTable = dataSet.Tables[i];
                Worksheet worksheet = new Worksheet();
                worksheet.Name = dataTable.TableName;
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    worksheet[1, j + 1] = new Cell(dataTable.Columns[j].ColumnName);
                }
                for (int k = 0; k < dataTable.Rows.Count; k++)
                {
                    for (int l = 0; l < dataTable.Columns.Count; l++)
                    {
                        worksheet[k + 2, l + 1] = new Cell(dataTable.Rows[k][l]);
                    }
                }

                worksheet.Tables.Rows[1].Height = 100;
                worksheet.Tables.Columns[1].Width = 100;
                //workSheet[1, 2].StyleId = workbook.Styles.Add(new Style
                //                                                  {
                //                                                      Font = new Font
                //                                                                 {
                //                                                                     Color = "red",
                //                                                                     Size = 16
                //                                                                 }
                //                                                      ,
                //                                                      Alignment = new Alignment {Horizontal = "Center"}
                //                                                  });
                //workSheet[1, 1].StyleId = workbook.Styles.Add(new Style
                //                                                  {
                //                                                      Font = new Font
                //                                                                 {
                //                                                                     Color = "red",
                //                                                                     Size = 16
                //                                                                 }
                //                                                      ,
                //                                                      Alignment = new Alignment {Horizontal = "Center"}
                //                                                  });
                //workbook.WorkSheets.Add(worksheet);

                //workbook.Save("file.xml");
                //Excel excel = new Excel();
                //Workbook workbook = excel.Open("file.xml");



                workbook.WorkSheets.Add(worksheet);
                workbook.Save(dataTable.TableName + ".xml");
            }
        }
예제 #13
0
        public static bool SaveExcel(Excel.IWorkbookBase workbook, string fileName)
        {
            if (File.Exists(fileName))
                File.Delete(fileName);

            using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
                return true;
            }
        }
예제 #14
0
 public void Show(Excel xls)
 {
     mExcel = xls;
     for (int i = 0; i < mExcel.Tables.Count; i++)
     {
         mExcel.Tables[i].SetCellTypeColumn(1, ExcelTableCellType.Label);
         mExcel.Tables[i].SetCellTypeColumn(3, ExcelTableCellType.Popup, new List<string>(){"1","2"});
         mExcel.Tables[i].SetCellTypeRow(1, ExcelTableCellType.Label);
         mExcel.Tables[i].SetCellTypeRow(2, ExcelTableCellType.Label);
     }
 }
예제 #15
0
 static void WriteXls()
 {
     Excel xls = new Excel();
     ExcelTable table = new ExcelTable();
     table.TableName = "test";
     string outputPath = Application.dataPath + "/Test/Test2.xlsx";
     xls.Tables.Add(table);
     xls.Tables[0].SetValue(1, 1, Random.Range(1000,100000).ToString());
     xls.ShowLog();
     ExcelHelper.SaveExcel(xls, outputPath);
 }
    protected void DownloadTemplate_Click(object sender, EventArgs e)
    {
        //Instantiate an instance of license and set the license file through its path
        Aspose.Excel.License license = new Aspose.Excel.License();
        license.SetLicense("Aspose.Excel.lic");

        Excel excel = new Excel();
        string path = MapPath("../..") + @"\templates\";
        string designerFile = path + "UserCredentialsTemplate.xls";
        excel.Open(designerFile);
        excel.Save("User_Credentials.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);
    }
예제 #17
0
파일: ExcelCov.cs 프로젝트: Centny/cswf.doc
 public static void Enqueue(Excel app)
 {
     try
     {
         app.Book.Close(false, null, null);
         Cached.Enqueue(app);
     }
     catch (Exception e)
     {
         L.E(e, "Close Excel fail with error->", e.Message);
         app.Dispose();
     }
 }
예제 #18
0
 static void test()
 {
     Excel xls = new Excel();
     ExcelTable table = new ExcelTable();
     table.TableName = "test";
     string outputPath = Application.dataPath + "/Test/Test2.xlsx";
     xls.Tables.Add(table);
     xls.Tables[0].SetValue(1, 1, "1");
     xls.Tables[0].SetValue(1, 2, "2");
     xls.Tables[0].SetValue(2, 1, "3");
     xls.Tables[0].SetValue(2, 2, "4");
     xls.ShowLog();
     ExcelHelper.SaveExcel(xls, outputPath);
 }
예제 #19
0
        public static void NativeExcel_CreateExcel(
            DataTable Dt
            , ClsExcel_Columns Columns
            , string SaveFileName = ""
            , Excel.XlFileFormat FileFormat =  Excel.XlFileFormat.xlExcel5
            , string Title = "")
        {
            Excel.Application Obj_Excel = new Excel.Application();

            Excel.Workbook owbook = Obj_Excel.Workbooks.Add();
            Excel.Worksheet owsheet = owbook.Worksheets.Add();

            Int32 RowCt = 2;
            Int32 ColCt = 1;

            foreach (ClsExcel_Columns.Str_Columns? Obj in Columns.pObj)
            {
                owsheet.Cells[RowCt, ColCt].Value = Obj.Value.FieldDesc;
                owsheet.Cells[RowCt, ColCt].Font.Bold = true;
                Excel.Range Inner_ExRange =
                    owsheet.Range[
                        Do_Methods.GenerateChr(ColCt)
                        + RowCt.ToString()
                        + ":"
                        + Do_Methods.GenerateChr(ColCt)
                        + (RowCt + Dt.Rows.Count).ToString()];
                Inner_ExRange.NumberFormat = Obj.Value.NumberFormat;
                ColCt++;
            }

            RowCt++;
            ColCt = 1;

            Excel.Range ExRange =
                owsheet.Range[
                Do_Methods.GenerateChr(ColCt)
                + RowCt.ToString()
                + ":"
                + Do_Methods.GenerateChr(ColCt + Columns.pObj.Count)
                + (RowCt + Dt.Rows.Count - 1).ToString()];

            ExRange.Value = Do_Methods.ConvertDataTo2DimArray(Dt, Columns.pFieldName);
            owsheet.Range["A1;IV65536"].AutoFit();

            if (SaveFileName == "")
            { SaveFileName = "Excel_File"; }

            owsheet.Range["A1:A1"].Select();
            owbook.SaveAs(SaveFileName, FileFormat);
        }
예제 #20
0
 public static void SaveExcel(Excel xls, string path)
 {
     FileInfo output = new FileInfo(path);
     ExcelPackage ep = new ExcelPackage();
     for (int i = 0; i < xls.Tables.Count; i++)
     {
         ExcelTable table = xls.Tables[i];
         ExcelWorksheet sheet = ep.Workbook.Worksheets.Add(table.TableName);
         for (int row = 1; row <= table.NumberOfRows; row++) {
             for (int column = 1; column <= table.NumberOfColumns; column++) {
                 sheet.Cells[row, column].Value = table.GetValue(row, column);
             }
         }
     }
     ep.SaveAs(output);
 }
예제 #21
0
        public static Regression Run(Excel app, Workbook wkbk, Range xRng, Range yRng)
        {
            Worksheet outWs = wkbk.GetNewWorksheet();
            Range outRng = outWs.GetA1Range();

            XL.Workbook atvbaen = app.XlApplication.Workbooks.Open(@"C:\Program Files\Microsoft Office 15\root\office15\Library\Analysis\ATPVBAEN.XLAM");
            atvbaen.RunAutoMacros(XL.XlRunAutoMacro.xlAutoOpen);

            app.XlApplication.Run(@"Regress", yRng.XlRange, xRng.XlRange, Missing.Value, Missing.Value, Missing.Value, outRng.XlRange);

            Range usedRng = outWs.GetUsedRange();

            object[,] values = usedRng.Values;

            Regression retValue = Regression.ParseValues(values);
            return retValue;
        }
예제 #22
0
    protected void Page_Load(object sender, EventArgs e)
    {
        string actionKey = string.Empty;
        if (Request.QueryString.AllKeys.Contains("actionKey"))
        {
            actionKey = Request.QueryString["actionKey"];
        }

        DataTable dt = new Custom_PerFormsService().GetDataTableAll(actionKey, null);
        string fileName = "report.xls";
        string mimeType = new MIMEType().GetMIMEType(new MIMEType().GetExtension(fileName));

        byte[] buffer = new Excel().GetExcelBufferFromDataTable(dt);
        Response.Clear();
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
        Response.ContentType = mimeType;
        Response.OutputStream.Write(buffer, 0, buffer.Length);
        Response.OutputStream.Close();
        Response.End();
    }
예제 #23
0
        public static bool Render(System.Web.HttpResponseBase Response, 
            Excel.IWorkbookBase workbook, string fileName)
        {
            // AiLib.Report.Excel.IWorkbookBase workbook

            // http://dotnetslackers.com/articles/aspnet/Create-Excel-Spreadsheets-Using-NPOI.aspx
            // Save the Excel spreadsheet to a MemoryStream and return it to the client

            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);

                string saveAsFileName = fileName;
                Response.ContentType = ContentExcel;
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }

            return true;
        }
예제 #24
0
        /// <summary>
        /// 合并单元格,并赋值,对指定WorkSheet操作
        /// </summary>
        /// <param name="beginRowIndex">开始行索引</param>
        /// <param name="beginColumnIndex">开始列索引</param>
        /// <param name="endRowIndex">结束行索引</param>
        /// <param name="endColumnIndex">结束列索引</param>
        /// <param name="text">合并后Range的值</param>
        private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
        {
            if (sheet == null)
                return;

            range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);

            range.ClearContents();		//先把Range内容清除,合并才不会出错
            range.MergeCells = true;
            range.Value = text;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
        }
예제 #25
0
 private String GetSheetValue(Excel.Worksheet worksheet, string field)
 {
     Excel.Range range = worksheet.get_Range(field, field);
     if (range == null) return "";
     if (range.Cells.Value2 == null) return "";
     return range.Cells.Value2.ToString();
 }
 public Range GetRange(Excel excel, int firstUsedRow, int firstUsedColumn)
 {
     return(excel.GetRange(firstUsedRow, firstUsedColumn));
 }
예제 #27
0
 private static void ReadDataFromExcelFile()
 {
     Console.OutputEncoding = Encoding.UTF8;
     Console.WriteLine("Step three: read data from file");
     Excel.ReadFromExcelFile();
 }
예제 #28
0
        /// <summary>
        /// 根据传入参数,将数据导出到Excel中(日报,月报,年报)
        /// </summary>
        /// <param name="basePath">App_Data</param>
        /// <param name="buildId"></param>
        /// <param name="energyCode"></param>
        /// <param name="circuits"></param>
        /// <param name="type"></param>
        /// <param name="date"></param>
        /// <returns>Excel实体类,包含Excel文件的byte数组与Excel文件的名称</returns>
        public Excel ExportCircuitReportToExcel(string basePath, string buildId, string energyCode, string[] circuits, string type, string date)
        {
            type = type.ToUpper();
            string templatePath = basePath + "/DayReportTemplate.xls";
            string reportType   = " 日报(" + date + ")";

            if (type == "MM")
            {
                reportType   = " 月报(" + date + ")";
                date        += "-01";
                templatePath = basePath + "/MonthReportTemplate.xls";
            }
            else if (type == "YY")
            {
                reportType   = " 年报(" + date + ")";
                date        += "-01-01";
                templatePath = basePath + "/YearReportTemplate.xls";
            }
            else if (type == "DD")
            {
                reportType   = " 日报(" + date + ")";
                templatePath = basePath + "/DayReportTemplate.xls";
            }

            //根据模板生成Excel
            FileStream   file  = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
            HSSFWorkbook book  = new HSSFWorkbook(file);
            HSSFSheet    sheet = (HSSFSheet)book.GetSheet("Sheet1");

            //创建单元格格式:数字型单元格默认取小数点后两位
            ICellStyle style = book.CreateCellStyle();

            style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");


            //获取当前建筑名
            IHomeDbContext homeContext = new HomeDbContext();
            BuildInfo      build       = homeContext.GetBuildById(buildId);

            //获取能源类型
            EnergyItemDict energyItem = context.GetUnitByEnergyCode(energyCode);

            DateTime now = Utils.Util.ConvertString2DateTime(date, "yyyy-MM-dd");

            //string[] circuitIds = circuits.Split(',');

            List <ReportValue> data = context.GetReportValueList(circuits, now.ToShortDateString(), type);

            var totalList = data.GroupBy(d => new { d.Time })
                            .Select(group => new ReportValue
            {
                Id    = "Total",
                Name  = "总计",
                Time  = group.Key.Time,
                Value = group.Sum(p => p.Value)
            }).ToList();

            data.AddRange(totalList);

            //设置Excel标题
            sheet.GetRow(0).GetCell(0).SetCellValue(build.BuildName + reportType);

            //设置Excel中报表的能源类别
            sheet.GetRow(1).GetCell(1).SetCellValue(energyItem.EnergyItemName);
            //设置Excel中报表的能源单位
            sheet.GetRow(1).GetCell(5).SetCellValue(energyItem.EnergyItemUnit);

            //根据传入circuitIds填充excel
            int rowId = 0;

            for (int i = 0; i < circuits.Length + 1; i++)
            {
                //使用lamda表达式筛选List中Id与传入的Id对应的仪表:一次填充一行Excel
                List <ReportValue> current;
                if (i == circuits.Length)
                {
                    current = data.FindAll(p => p.Id == "Total");
                }
                else
                {
                    current = data.FindAll(p => p.Id == circuits[i]);
                }

                if (current.Count > 0)
                {
                    IRow row = sheet.CreateRow(rowId + 3);
                    row.CreateCell(0).SetCellValue(current[0].Name);
                    decimal total = 0;
                    switch (type)
                    {
                    case "DD":
                        foreach (var item in current)
                        {    //遍历筛选出列表中当前回路中每个小时的数据,填充到Excel中
                            //当前行的第一列已经填充回路名称,根据时间设置向右偏移一位,将对应数据写入到当前单元格,并设置格式
                            if (item.Time != null)
                            {
                                DateTime time = Convert.ToDateTime(item.Time);
                                row.CreateCell(time.Hour + 1).SetCellValue((double)item.Value);
                                row.GetCell(time.Hour + 1).CellStyle = style;
                                total += Convert.ToDecimal(item.Value);
                            }
                        }
                        row.CreateCell(25).SetCellValue((double)total);
                        row.GetCell(25).CellStyle = style;
                        break;

                    case "MM":
                        foreach (var item in current)
                        {
                            if (item.Time != null)
                            {
                                DateTime time = Convert.ToDateTime(item.Time);
                                row.CreateCell(time.Day).SetCellValue((double)item.Value);
                                row.GetCell(time.Day).CellStyle = style;
                                total += Convert.ToDecimal(item.Value);
                            }
                        }
                        row.CreateCell(32).SetCellValue((double)total);
                        row.GetCell(32).CellStyle = style;
                        break;

                    case "YY":
                        foreach (var item in current)
                        {
                            if (item.Time != null)
                            {
                                DateTime time = Convert.ToDateTime(item.Time);
                                row.CreateCell(time.Month).SetCellValue((double)item.Value);
                                row.GetCell(time.Month).CellStyle = style;
                                total += Convert.ToDecimal(item.Value);
                            }
                        }
                        row.CreateCell(13).SetCellValue((double)total);
                        row.GetCell(13).CellStyle = style;
                        break;
                    }

                    rowId++;
                }
            }

            Excel excel = new Excel();

            excel.Name = build.BuildName + reportType + "[" + Guid.NewGuid().ToString("N") + "].xls";

            using (MemoryStream stream = new MemoryStream())
            {
                book.Write(stream);
                stream.Seek(0, SeekOrigin.Begin);
                excel.Data = stream.ToArray();
            }

            return(excel);
        }
        public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, string destinationCode, bool status, int transaction, string packingList, int offset, string username)
        {
            var Query = GetReportQuery(dateFrom, dateTo, destinationCode, status, transaction, packingList, offset, username);

            Query = Query.OrderByDescending(b => b.LastModifiedUtc);
            DataTable result = new DataTable();

            //No	Unit	Budget	Kategori	Tanggal PR	Nomor PR	Kode Barang	Nama Barang	Jumlah	Satuan	Tanggal Diminta Datang	Status	Tanggal Diminta Datang Eksternal


            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Sumber Penyimpanan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tujuan Penyimpanan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Packing List", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Barcode", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Deskripsi", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Size", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "RO", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Satuan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Quantity", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Diterima", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Ekspedisi", DataType = typeof(String)
            });
            //result.Columns.Add(new DataColumn() { ColumnName = "Jumlah Diminta", DataType = typeof(double) });
            //result.Columns.Add(new DataColumn() { ColumnName = "Satuan Diminta", DataType = typeof(String) });
            //result.Columns.Add(new DataColumn() { ColumnName = "Tanggal diminta datang", DataType = typeof(String) });
            //result.Columns.Add(new DataColumn() { ColumnName = "Tanggal diminta datang PO Eksternal", DataType = typeof(String) });
            //result.Columns.Add(new DataColumn() { ColumnName = "Jumlah Deal PO Eksternal", DataType = typeof(double) });
            //result.Columns.Add(new DataColumn() { ColumnName = "Satuan Deal PO Eksternal", DataType = typeof(String) });
            //result.Columns.Add(new DataColumn() { ColumnName = "Status PR", DataType = typeof(String) });
            //result.Columns.Add(new DataColumn() { ColumnName = "Status Barang", DataType = typeof(String) });
            if (Query.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", "", "", "", "", "", 0, "", ""); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                int index = 0;
                foreach (var item in Query)
                {
                    index++;
                    string date = item.date == null ? "-" : item.date.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    //string prDate = item.expectedDeliveryDatePR == new DateTime(1970, 1, 1) ? "-" : item.expectedDeliveryDatePR.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    //string epoDate = item.expectedDeliveryDatePO == new DateTime(1970, 1, 1) ? "-" : item.expectedDeliveryDatePO.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    result.Rows.Add(item.date, item.sourceName, item.destinationName, item.packingList, item.itemCode, item.itemName, item.itemSize, item.itemArticleRealizationOrder, item.itemUom, item.Quantity, item.isReceived, item.isDistributed);
                }
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Territory")
            }, true));
        }
예제 #30
0
        internal static string InTouchDIntErrorNumber             = "XlLogErrorNumber";    //An InTouch weiterzugebene Fehlernummer
        #endregion

        #endregion

        static void Main(string[] args) //Fehlernummern siehe Log.cs 0101ZZ
        {
            #region Vorbereitende Abfragen
            try
            {
                CmdArgs = args;

                if (CmdArgs.Length < 1)
                {
                    AppStartedBy = Environment.UserName;
                }
                else
                {
                    AppStartedBy = CmdArgs[0].Remove(0, 1);
                }
                Config.LoadConfig();

                Log.Write(Log.Cat.OnStart, Log.Prio.LogAlways, 010101, string.Format("Gestartet durch {0}, Debug {1}, V{2}", AppStartedBy, Log.DebugWord, System.Reflection.Assembly.GetExecutingAssembly().GetName().Version));

                #region PDF erstellen per Drag&Drop
                try
                {
                    if (CmdArgs.Length > 0)
                    {
                        if (File.Exists(CmdArgs[0]) && Path.GetExtension(CmdArgs[0]) == ".xlsx")
                        {
                            //Wenn der Pfad zu einer Excel-Dateie übergebenen wurde, diese in PDF umwandeln, danach beenden
                            Console.WriteLine("Wandle Excel-Dateie in PDF " + CmdArgs[0]);
                            Log.Write(Log.Cat.PdfWrite, Log.Prio.LogAlways, 010100, "Wandle Excel-Datei in PDF " + CmdArgs[0]);
                            Pdf.CreatePdf(CmdArgs[0]);
                            Console.WriteLine("Exel-Datei " + CmdArgs[0] + " umgewandelt in PDF.\r\nBeliebige Taste drücken zum Beenden...");
                            Console.ReadKey();
                            return;
                        }
                        else if (!File.Exists(CmdArgs[0]) && Directory.Exists(CmdArgs[0]))
                        {
                            //Alle Excel-Dateien im übergebenen Ordner in PDF umwandeln, danach beenden
                            Console.WriteLine("Wandle alle Excel-Dateien in PDF im Ordner " + CmdArgs[0]);
                            Log.Write(Log.Cat.PdfWrite, Log.Prio.LogAlways, 010100, "Wandle alle Excel-Dateien in PDF im Ordner " + CmdArgs[0]);
                            Pdf.CreatePdf4AllXlsxInDir(CmdArgs[0], false);
                            Console.WriteLine("Exel-Dateien umgewandelt in " + CmdArgs[0] + "\r\nBeliebige Taste drücken zum Beenden...");
                            Console.ReadKey();
                            return;
                        }
                    }
                }
                catch
                {
                    Log.Write(Log.Cat.PdfWrite, Log.Prio.Error, 010118, string.Format("Fehler beim Erstellen von PDF durch Drag'n'Drop. Aufrufargumente prüfen."));
                }
                #endregion

                EmbededDLL.LoadDlls();

                bool makerIsRunning = Process.GetProcessesByName("wm").Length != 0;
                if (makerIsRunning)
                {
                    Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010102, "Das Programm kann nicht zusammen mit dem InTouch WindowMaker / Manager ausgeführt werden und wird deshalb beendet.");
                    Console.WriteLine("ACHTUNG: Das Programm kann nicht zusammen mit dem InTouch WindowMaker / Manager ausgeführt werden und wird deshalb beendet.");
                    Tools.Wait(10);
                    return;
                }


                bool viewerIsRunning = Process.GetProcessesByName("view").Length != 0;

                if (!viewerIsRunning)
                {
                    Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010103, "Das Programm kann nicht ohne den InTouch Viewer ausgeführt werden und wird deshalb beendet.");
                    Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne den InTouch Viewer ausgeführt werden und wird deshalb beendet.");
                    Tools.Wait(10);
                    return;
                }

                if (!File.Exists(NativeMethods.PtaccPath))
                {
                    Log.Write(Log.Cat.InTouchDB, Log.Prio.Info, 010104, string.Format("Datei für 64bit-OS nicht gefunden: " + NativeMethods.PtaccPath));

                    if (!File.Exists(NativeMethods32.PtaccPath))
                    {
                        Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010104, string.Format("Datei für 64bit oder 32bit-OS nicht gefunden: \r\n" +
                                                                                           NativeMethods.PtaccPath + "\r\n" +
                                                                                           NativeMethods32.PtaccPath + "\r\n"));
                        Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne die Datei " + Path.GetFileName(NativeMethods32.PtaccPath) + " ausgeführt werden und wird deshalb beendet. Beachte Log-Datei.");
                        Tools.Wait(10);
                        return;
                    }
                    else
                    {
                        InTouch.Is32BitSystem = true;
                    }
                }
                else
                {
                    InTouch.Is32BitSystem = false;
                }

                if (!File.Exists(NativeMethods.WwheapPath))
                {
                    Log.Write(Log.Cat.InTouchDB, Log.Prio.Info, 010104, string.Format("Datei für 64bit-OS nicht gefunden: " + NativeMethods.WwheapPath));

                    if (!File.Exists(NativeMethods32.WwheapPath))
                    {
                        Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010104, string.Format("Datei für 64bit oder 32bit-OS nicht gefunden: \r\n" +
                                                                                           NativeMethods.WwheapPath + "\r\n" +
                                                                                           NativeMethods32.WwheapPath + "\r\n"));
                        Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne die Datei " + Path.GetFileName(NativeMethods32.WwheapPath) + " ausgeführt werden und wird deshalb beendet. Beachte Log-Datei.");
                        Tools.Wait(10);
                        return;
                    }
                    else
                    {
                        InTouch.Is32BitSystem = true;
                    }
                }
                else
                {
                    InTouch.Is32BitSystem = false;
                }



                //if (!File.Exists(NativeMethods.WwheapPath))
                //{
                //    Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010107, string.Format("Datei nicht gefunden: " + NativeMethods.WwheapPath));
                //    Console.WriteLine("ACHTUNG: Das Programm kann nicht ohne die Datei " + NativeMethods.WwheapPath + " ausgeführt werden und wird deshalb beendet. Beachte Log-Datei.");

                //    if (Path.GetDirectoryName(NativeMethods.WwheapPath).Contains(" (x86)"))
                //    {
                //        Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010108, string.Format("Dieses Programm ist für ein 64-Bit Betriebssystem ausgelegt."));
                //    }
                //    else if (Path.GetDirectoryName(NativeMethods.PtaccPath).StartsWith(@"C:\Program Files\"))
                //    {
                //        Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010109, string.Format("Dieses Programm ist für ein 32-Bit Betriebssystem ausgelegt."));
                //    }

                //    Tools.Wait(10);
                //    return;
                //}

                if (!File.Exists(Excel.XlTemplateDayFilePath))
                {
                    Log.Write(Log.Cat.InTouchDB, Log.Prio.Error, 010110, string.Format("Vorlage für Tagesdatei nicht gefunden unter: " + Excel.XlTemplateDayFilePath));
                    //AppErrorOccured = true;
                }

                if (!File.Exists(Excel.XlTemplateMonthFilePath))
                {
                    Log.Write(Log.Cat.ExcelRead, Log.Prio.Warning, 010111, string.Format("Keine Vorlage für Monatsdatei gefunden."));
                    //kein Fehler
                }

                if ((bool)InTouch.ReadTag(InTouchDiscXlLogFlag) != true)
                {
                    Log.Write(Log.Cat.InTouchVar, Log.Prio.Error, 010112, "Freigabe-Flag >" + InTouchDiscXlLogFlag + "< wurde nicht in InTouch gesetzt. Das Programm wird beendet.");
                    //AppErrorOccured = true;
                    return;
                }

                string Operator = (string)InTouch.ReadTag("$Operator");
                Log.Write(Log.Cat.Info, Log.Prio.Info, 010113, "Angemeldet in InTouch: >" + Operator + "<");

                Scheduler.CeckOrCreateTaskScheduler();

                if (!Directory.Exists(Excel.XlArchiveDir))
                {
                    try
                    {
                        Directory.CreateDirectory(Excel.XlArchiveDir);
                    }
                    catch (Exception ex)
                    {
                        Log.Write(Log.Cat.FileSystem, Log.Prio.Error, 010114, string.Format("Archivordner konnte nicht gefunden oder erstellt werden: {0}\r\n\t\t Typ: {1} \r\n\t\t Fehlertext: {2}  \r\n\t\t InnerException: {3}", Excel.XlArchiveDir, ex.GetType().ToString(), ex.Message, ex.InnerException));
                        return;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010115, string.Format("Fehler beim initialisieren der Anwendung: Typ: {0} \r\n\t\t Fehlertext: {1}  \r\n\t\t InnerException: {2}", ex.GetType().ToString(), ex.Message, ex.InnerException));
                return;
            }
            #endregion

            Excel.XlFillWorkbook();

            Print.PrintRoutine();

            #region Diese *.exe beenden
            InTouch.SetExcelAliveBit(Program.AppErrorOccured);

            if (AppErrorOccured)
            {
                Log.Write(Log.Cat.OnStart, Log.Prio.Error, 010116, "XlLog.exe beendet. Es ist ein Fehler aufgetreten.\r\n\r\n");
            }
            else
            {
                Log.Write(Log.Cat.OnStart, Log.Prio.Info, 010117, "XlLog.exe ohne Fehler beendet.\r\n");
            }

            // Bei manuellem Start Fenster kurz offen halten.
            if (AppStartedBy == Environment.UserName)
            {
                Tools.Wait(Tools.WaitToClose);
            }
            #endregion
        }
예제 #31
0
 public ExcelParameter(D.Point StartCell, D.Point EndCell, string value, params Microsoft.Office.Interop.Excel.XlBordersIndex[] borders)
     : this(Excel.ColNumToEx(StartCell.X) + StartCell.Y.ToString(), Excel.ColNumToEx(EndCell.X) + EndCell.Y.ToString(), value, MExcel.XlHAlign.xlHAlignCenter, null, borders)
 {
 }
예제 #32
0
    public int Execute()
    {
        string    strGUID       = Guid.NewGuid().ToString();
        int       realImportNum = 0;
        Excel     _excel        = new Excel();
        DataTable dt            = new DataTable();

        switch (TextType)
        {
        case "ASTM":
            s_ASTM ASTM = new s_ASTM();
            realImportNum = ASTM.Execute(IiSheet, strGUID);
            break;

        case "Digitimes":
            s_Digitimes Digi = new s_Digitimes();
            realImportNum = Digi.Execute(IiSheet, strGUID);
            break;

        case "EV":
            s_EV EV = new s_EV();
            realImportNum = EV.Execute(IiSheet, strGUID);
            break;

        case "Goldfire":
            s_IHS IHS = new s_IHS();
            realImportNum = IHS.Execute(IiSheet, strGUID);
            break;

        case "JCR":
            s_JCR JCR = new s_JCR();
            realImportNum = JCR.Execute(IiSheet, strGUID);
            break;

        case "nature":
            s_nature nature = new s_nature();
            realImportNum = nature.Execute(IiSheet, strGUID);
            break;

        case "Oxford":
            s_Oxford Oxford = new s_Oxford();
            realImportNum = Oxford.Execute(IiSheet, strGUID);
            break;

        case "Science":
            s_Science Science = new s_Science();
            realImportNum = Science.Execute(IiSheet, strGUID);
            break;

        //
        //phase II below
        //
        case "ACM":
            s_ACM ACM = new s_ACM();
            realImportNum = ACM.Execute(IiSheet, strGUID);
            break;

        case "ACS":
            s_ACS ACS = new s_ACS();
            realImportNum = ACS.Execute(IiSheet, strGUID);
            break;

        case "IEEE":
            s_IEEE IEEE = new s_IEEE();
            realImportNum = IEEE.Execute(IiSheet, strGUID);
            break;

        case "SDOL":
            s_SDOL SDOL = new s_SDOL();
            realImportNum = SDOL.Execute(IiSheet, strGUID);
            break;

        case "SPIE":
            s_SPIE SPIE = new s_SPIE();
            realImportNum = SPIE.Execute(IiSheet, strGUID);
            break;

        case "TI":
            s_TI TI = new s_TI();
            realImportNum = TI.Execute(IiSheet, strGUID);
            break;

        case "Wiley":
            s_Wiley Wiley = new s_Wiley();
            realImportNum = Wiley.Execute(IiSheet, strGUID);
            break;

        case "天下":
            s_TangShang TangShang = new s_TangShang();
            realImportNum = TangShang.Execute(IiSheet, strGUID);
            break;

        case "萬方":
            s_WangFang WangFang = new s_WangFang();
            realImportNum = WangFang.Execute(IiSheet, strGUID);
            break;

        case "通用":
            dt = _excel.ImportExecl(IiSheet);
            s_Utility Utility = new s_Utility();
            realImportNum = Utility.Execute(dt, strGUID, Year, Month);
            break;

        case "工程":
            dt = _excel.ImportExecl(IiSheet);
            s_Engineering Engineering = new s_Engineering();
            realImportNum = Engineering.Execute(dt, strGUID);
            break;

        case "聯合知識庫":
            dt = _excel.ImportExecl(IiSheet);
            s_Union Union = new s_Union();
            realImportNum = Union.Execute(dt, strGUID);
            break;

        case "SciFinder":
            dt = _excel.ImportExecl(IiSheet);
            s_SciFinder SciFinder = new s_SciFinder();
            realImportNum = SciFinder.Execute(dt, strGUID);
            break;
        }

        //每一次匯入都要做log,判斷等不等於零是因為沒資料就不要做log
        if (TextType.Trim() != "通用" && realImportNum != 0)
        {
            string   yearMonth = Year + Month + "01";
            DateTime dTime     = DateTime.ParseExact(yearMonth, "yyyyMMdd", CultureInfo.InvariantCulture);
            //通用的log自己在裡面做
            saveLog(realImportNum, strGUID, TextType, dTime);
        }

        //做完匯入之後再把工研人塞入
        UpdateEmpno(strGUID);

        //由於在通用的表單裡面,有些資料會有email,但是沒IP跟工號,
        //而也有可能是有工號沒IP沒EMAIL
        //所以要再等根據IP更新之後
        //2016/02/02 由於Goldfile有可能會用中文姓名+部門+單位來當WHERE條件,所以在這裡多一步去補上
        if ((TextType.Trim() == "通用" ||
             TextType.Trim() == "Goldfire" ||
             TextType.Trim() == "TI" ||
             TextType.Trim() == "聯合知識庫" ||
             TextType.Trim() == "SciFinder") && realImportNum != 0)
        {
            UpdateUtility(strGUID);
        }
        return(realImportNum);
    }
예제 #33
0
 /// <summary>
 /// конструктор с указанием координта ячеек в цифровом виде
 /// (нумерация с 1 начинается)
 /// </summary>
 /// <param name="f"></param>
 /// <param name="Align"></param>
 /// <param name="nameOfExcel"></param>
 /// <param name="nameOfEndMergeExcel"></param>
 /// <param name="value"></param>
 public ExcelParameter(MExcel.XlHAlign Align, D.Point nameOfExcel, string value)
     : this(Excel.ColNumToEx(nameOfExcel.X) + nameOfExcel.Y.ToString(), Excel.ColNumToEx(nameOfExcel.X) + nameOfExcel.Y.ToString(),
            value, Align, null, null)
 {
 }
 private void Button_Click(object sender, System.Windows.RoutedEventArgs e)
 {
     Excel.ExportToExcelAndCsv(grilla);
 }
        public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var Query = GetReportQuery(dateFrom, dateTo, offset);

            Query = Query.OrderByDescending(b => b.ReceiptDate);

            var       QtyTotal   = Query.Sum(x => x.Quantity);
            var       PriceTotal = Math.Round(Query.Sum(x => x.Price), 2);
            DataTable result     = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No Bon Terima", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Bon", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Asal barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No Bon Pengeluaran Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "RO", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kode Komoditi", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Komoditi", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Qty", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Satuan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Price", DataType = typeof(double)
            });

            if (Query.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", "", "", "", "", 0, "", 0); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                int index = 0;
                foreach (var item in Query)
                {
                    index++;
                    //DateTimeOffset date = item.date ?? new DateTime(1970, 1, 1);
                    //string dateString = date == new DateTime(1970, 1, 1) ? "-" : date.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    result.Rows.Add(index, item.ReceiptNoteNo, item.ReceiptDate.AddHours(offset).ToString("dd MMM yyyy", new CultureInfo("id-ID")), item.UnitFromCode, item.ExpenditureGoodNo, item.RONo, item.ComodityCode, item.ComodityName, item.Quantity, item.UomUnit);
                }

                result.Rows.Add("", "T O T A L .......", "", "", "", "", "", "", QtyTotal, "", PriceTotal);
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Report Pengeluaran Gudang Sisa Barang Jadi")
            }, true));
        }
예제 #36
0
 public ColHeaderPanel(Excel p_excel)
     : base(p_excel, SheetArea.ColumnHeader)
 {
 }
        public MemoryStream GenerateExcel(string storageCode, string productCode, string type, DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var query = GetReportQuery(storageCode, productCode, type, dateFrom, dateTo, offset);

            query = query.OrderByDescending(b => b.LastModifiedUtc);
            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Storage", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Reference number", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "RequestType Reference", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Date", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Item name", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Uom", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Before", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Quantity", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "After", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Type", DataType = typeof(string)
            });
            if (query.ToArray().Count() == 0)
            {
                result.Rows.Add(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, 0, 0, 0, string.Empty); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                int index = 0;
                foreach (var item in query)
                {
                    index++;
                    //DateTimeOffset date = item.date ?? new DateTime(1970, 1, 1);
                    //string dateString = date == new DateTime(1970, 1, 1) ? "-" : date.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    result.Rows.Add(index, item.StorageName, item.ReferenceNo, item.ReferenceType, item.Date.ToString("dd MMM yyyy", new CultureInfo("id-ID")), item.ProductName, item.UomUnit, item.Before,
                                    item.Quantity, item.After, item.Type);
                }
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Territory")
            }, true));
        }
        public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var Query = GetMachineMutationReport(dateFrom, dateTo, offset);

            Query = Query.OrderBy(b => b.KodeBarang);
            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kode Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nama Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Sat", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Saldo Awal", DataType = typeof(Double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Pemasukan", DataType = typeof(Double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Pengeluaran", DataType = typeof(Double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Penyesuaian", DataType = typeof(Double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Saldo Buku", DataType = typeof(Double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Stock Opname", DataType = typeof(Double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Selisih", DataType = typeof(Double)
            });
            if (Query.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", "", "", "", "", "", ""); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                foreach (var item in Query)
                {
                    result.Rows.Add((item.KodeBarang), item.NamaBarang, item.UnitQtyName, item.SaldoAwal, item.Pemasukan, item.Pengeluaran, item.Penyesuaian, item.SaldoBuku, item.StockOpname, item.Selisih);
                }
            }
            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Territory")
            }, true));
        }
예제 #39
0
 public SetValueCommand(Excel p_excel, DataValidationListButtonInfo info)
 {
     _info  = info;
     _excel = p_excel;
 }
        public MemoryStream GenerateExcelEPODODuration(string unit, string duration, DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var Query = GetEPODODurationReportQuery(unit, duration, dateFrom, dateTo, offset);

            Query = Query.OrderByDescending(b => b.prCreatedDate);
            DataTable result = new DataTable();

            //No	Unit	Budget	Kategori	Tanggal PR	Nomor PR	Kode Barang	Nama Barang	Jumlah	Satuan	Tanggal Diminta Datang	Status	Tanggal Diminta Datang Eksternal


            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Purchase Request", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Buat Purchase Request", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nomor Purchase Request", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Divisi", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Unit", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Budget", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kategori", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kode Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nama Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Jumlah Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Satuan Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Harga Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kode Supplier", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nama Supplier", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Terima PO Internal", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal PO Eksternal", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Buat PO Eksternal", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Target Datang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No PO Eksternal", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Surat Jalan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Datang Barang", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No Surat Jalan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Selisih Tanggal PO Eksternal - Surat Jalan (hari)", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nama Staff Pembelian", DataType = typeof(string)
            });

            if (Query.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                int index = 0;
                foreach (var item in Query)
                {
                    index++;
                    string prDate         = item.prDate == null ? "-" : item.prDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    string prCreatedDate  = item.prCreatedDate == new DateTime(1970, 1, 1) ? "-" : item.prCreatedDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    string poDate         = item.poDate == new DateTime(1970, 1, 1) ? "-" : item.poDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    string orderDate      = item.orderDate == new DateTime(1970, 1, 1) ? "-" : item.orderDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    string ePOCreatedDate = item.ePOCreatedDate == new DateTime(1970, 1, 1) ? "-" : item.ePOCreatedDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    string deliveryDate   = item.deliveryDate == new DateTime(1970, 1, 1) ? "-" : item.deliveryDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    string dODate         = item.dODate == new DateTime(1970, 1, 1) ? "-" : item.dODate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    string arrivalDate    = item.arrivalDate == new DateTime(1970, 1, 1) ? "-" : item.arrivalDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
                    result.Rows.Add(index, prDate, prCreatedDate, item.prNo, item.division, item.unit, item.budget, item.category, item.productCode, item.productName, item.dealQuantity, item.dealUomUnit, item.pricePerDealUnit, item.supplierCode, item.supplierName, poDate, orderDate, ePOCreatedDate, deliveryDate, item.ePONo, dODate, arrivalDate, item.dONo, item.dateDiff, item.staff);
                }
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Territory")
            }, true));
        }
        //public Tuple<List<UnitReceiptNoteGenerateDataViewModel>, int> GetDisplayReport(DateTime? dateFrom, DateTime? dateTo, int page, int size, string Order, int offset)

        //{
        //    var Query = GetReportQuery(dateFrom, dateTo, offset);

        //    Dictionary<string, string> OrderDictionary = JsonConvert.DeserializeObject<Dictionary<string, string>>(Order);
        //    if (OrderDictionary.Count.Equals(0))
        //    {
        //        Query = Query.OrderBy(b => b.UnitName).ThenBy(b => b.PRNo);
        //    }
        //    else
        //    {
        //        string Key = OrderDictionary.Keys.First();
        //        string OrderType = OrderDictionary[Key];
        //    }

        //    var q = Query.ToList();
        //    var index = 0;
        //    foreach (UnitReceiptNoteGenerateDataViewModel a in q)
        //    {
        //        index++;
        //    }

        //    Pageable<UnitReceiptNoteGenerateDataViewModel> pageable = new Pageable<UnitReceiptNoteGenerateDataViewModel>(Query, page - 1, size);
        //    List<UnitReceiptNoteGenerateDataViewModel> Data = pageable.Data.ToList<UnitReceiptNoteGenerateDataViewModel>();
        //    int TotalData = pageable.TotalCount;

        //    return Tuple.Create(Data, TotalData);
        //}

        public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var Query = GetReportQuery(dateFrom, dateTo, offset);

            Query = Query.OrderBy(b => b.URNNo);
            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NOMOR BON TERIMA UNIT", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "TANGGAL BON TERIMA UNIT", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "BAGIAN", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "KODE SUPPLIER", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NAMA SUPPLIER", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NOMOR SURAT JALAN", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "KETERANGAN", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "KODE BARANG", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NAMA BARANG", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "JUMLAH TERIMA", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "SATUAN BARANG", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "KETERANGAN BARANG", DataType = typeof(String)
            });

            if (Query.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", "", "", "", "", "", 0, "", ""); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                var index = 0;
                foreach (var item in Query)
                {
                    index++;
                    string URNDate = item.URNDate == new DateTime(1970, 1, 1) ? "-" : item.URNDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd/MM/yyyy", new CultureInfo("id-ID"));

                    result.Rows.Add(item.URNNo, URNDate, item.UnitName, item.SupplierCode, item.SupplierName, item.DONo, item.URNRemark, item.ProductCode, item.ProductName, item.Quantity, item.UOMUnit, item.Remark);
                }
            }
            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Sheet1")
            }, true));
        }
        public MemoryStream GenerateExcelMutation(DateTime dateFrom, DateTime dateTo)
        {
            var       Query  = GetReportQuery(dateFrom, dateTo);
            DataTable Result = new DataTable();

            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kode Barang", DataType = typeof(String)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nama Barang", DataType = typeof(String)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Satuan", DataType = typeof(String)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Saldo Awal", DataType = typeof(Double)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Pemasukan", DataType = typeof(Double)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Pengeluaran", DataType = typeof(Double)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Penyesuaian", DataType = typeof(Double)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Saldo Akhir", DataType = typeof(Double)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Stock Opname", DataType = typeof(Double)
            });
            Result.Columns.Add(new DataColumn()
            {
                ColumnName = "Selisih", DataType = typeof(Double)
            });
            if (Query.ToArray().Count() == 0)
            {
                Result.Rows.Add("", "", "", 0, 0, 0, 0, 0, 0, 0);
            }
            else
            {
                foreach (var item in Query)
                {
                    Result.Rows.Add(item.ClassificationCode, item.ClassificationName, item.UnitQtyName, item.SaldoAwal, item.Pemasukan, item.Pengeluaran, item.Penyesuaian, item.SaldoAkhir, item.StockOpname, item.Selisih);
                }
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(Result, "ScrapReject")
            }, true));
        }
        protected void imgExportToExcel_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
                Utility   objUtil = new Utility();
                DataTable dt      = new DataTable();
                dt = Session[clsConstant.SESS_TABLE] as DataTable;
                Excel          excel    = new Excel();
                ArrayList      list     = new ArrayList();
                clsExcelObject objExcel = null;
                List <Flags>   flag     = new List <Flags>();
                Flags          testFlag = null;
                //if (txtTopic.Text != "")
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "Topic";
                //    testFlag.value = txtTopic.Text;
                //    flag.Add(testFlag);
                //}
                //if (drpSector.SelectedIndex != 0)
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "Sector";
                //    testFlag.value = drpSector.SelectedItem.Text;
                //    flag.Add(testFlag);
                //}
                //if (txtFromDate.Text != "")
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "From Date";
                //    testFlag.value = txtFromDate.Text;
                //    flag.Add(testFlag);
                //}
                //if (txtToDate.Text != "")
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "From Date";
                //    testFlag.value = txtFromDate.Text;
                //    flag.Add(testFlag);
                //}
                //if (txtVenue.Text != "")
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "Venue";
                //    testFlag.value = txtVenue.Text;
                //    flag.Add(testFlag);
                //}
                //if (drpTargetAudience.SelectedIndex != 0)
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "Target Audience";
                //    testFlag.value = drpTargetAudience.SelectedItem.Text;
                //    flag.Add(testFlag);
                //}
                //if (drpInvite.SelectedIndex != 0)
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "Invite By";
                //    testFlag.value = drpInvite.SelectedItem.Text;
                //    flag.Add(testFlag);
                //}
                //if (drpNationalInternational.SelectedIndex != 0)
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "National / International";
                //    testFlag.value = drpNationalInternational.SelectedItem.Text;
                //    flag.Add(testFlag);
                //}
                //if (txtLstDate.Text != "")
                //{
                //    testFlag = new Flags();
                //    testFlag.key = "Last Date of Nomination";
                //    testFlag.value = txtLstDate.Text;
                //    flag.Add(testFlag);
                //}
                string[] header = new string[]
                {
                    "vsTopic",
                    "Sector",
                    "dtFromDate",
                    "dtToDate",
                    "vsVenue",
                    "iTargetID",
                    "iInvite",
                    "iSeats",
                    "CreatedFrom",
                    "dtLastDate",
                    "vsFileName"
                };
                string[] datacolumn = new string[grdWorkShopDetailsMDB.Columns.Count - 4];
                int      counter    = 0;
                for (int i = 1; i <= header.Length + 1; i++)
                {
                    if (grdWorkShopDetailsMDB.Columns[i].Visible != false)
                    {
                        objExcel = new clsExcelObject(header[counter], grdWorkShopDetailsMDB.Columns[i].HeaderText.Replace("<br />", ""));

                        list.Add(objExcel);
                        counter++;
                    }
                }
                //excel.FlagValues = flag;
                //excel.TableHeader = "WorkShop Details";
                excel.ExportToExcel(dt, "WorkShop Details MDB", list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
예제 #44
0
        public MemoryStream GenerateExcel(DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var       Query  = GetData(dateFrom, dateTo, offset);
            var       data   = Query.ToList();
            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NO", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "KOMODITI", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NAMA BUYER", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "U N I T", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NO INVOICE", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "T R U C K I N G", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "R/O", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "I  T  E  M", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "STYLE ORD / ART NO.", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "QUANTITY", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "SATUAN", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "AMOUNT", DataType = typeof(string)
            });

            List <(string, Enum, Enum)> mergeCells = new List <(string, Enum, Enum)>()
            {
            };

            int rowPosition = 2;

            if (data != null && data.Count > 0)
            {
                var grandTotalByUom = new List <TotalByUom>();

                foreach (var d in data)
                {
                    var ucFirstMergedRowPosition = rowPosition;
                    var ucLastMergedRowPosition  = rowPosition;

                    foreach (var buyer in d.Buyers)
                    {
                        var buyerFirstMergedRowPosition = rowPosition;
                        var buyerLastMergedRowPosition  = rowPosition;
                        var index = 0;
                        foreach (var detail in buyer.Details)
                        {
                            index++;
                            result.Rows.Add(index, d.ComodityName, buyer.Buyer, detail.UnitCode, detail.InvoiceNo, detail.TruckingDate.ToString("dd MMMM yyyy", new CultureInfo("id-ID")), detail.RO_Number, detail.ComodityName, detail.ComodityDesc, detail.Quantity, detail.UOMUnit, detail.Amount);

                            buyerLastMergedRowPosition = rowPosition++;

                            var currentUom = grandTotalByUom.FirstOrDefault(c => c.uom == detail.UOMUnit);
                            if (currentUom == null)
                            {
                                grandTotalByUom.Add(new TotalByUom
                                {
                                    uom      = detail.UOMUnit,
                                    quantity = detail.Quantity,
                                    amount   = detail.Amount
                                });
                            }
                            else
                            {
                                currentUom.quantity += detail.Quantity;
                                currentUom.amount   += detail.Amount;
                            }
                        }

                        result.Rows.Add(null, null, "SUB TOTAL", null, null, null, null, null, null, buyer.Quantities, null, buyer.Amounts);

                        mergeCells.Add(($"B{rowPosition}:G{rowPosition}", ExcelHorizontalAlignment.Right, ExcelVerticalAlignment.Bottom));

                        ucLastMergedRowPosition = rowPosition++;
                    }

                    if (ucFirstMergedRowPosition != ucLastMergedRowPosition)
                    {
                        mergeCells.Add(($"A{ucFirstMergedRowPosition}:A{ucLastMergedRowPosition}", ExcelHorizontalAlignment.Left, ExcelVerticalAlignment.Top));
                    }
                }

                result.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, null);
                result.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, null);

                rowPosition++;
                foreach (var i in Enumerable.Range(0, grandTotalByUom.Count))
                {
                    if (i == 0)
                    {
                        result.Rows.Add(null, null, "GRAND TOTAL", grandTotalByUom[i].quantity, grandTotalByUom[i].uom, null, grandTotalByUom[i].amount, null, null, "GRAND TOTAL", data.Sum(d => d.Buyers.Sum(b => b.Details.Sum(dtl => dtl.Amount))), null);
                    }
                    else
                    {
                        result.Rows.Add(null, null, null, grandTotalByUom[i].quantity, grandTotalByUom[i].uom, null, grandTotalByUom[i].amount, null, null, null, null, null);
                    }
                    mergeCells.Add(($"D{++rowPosition}:D{rowPosition}", ExcelHorizontalAlignment.Right, ExcelVerticalAlignment.Bottom));
                }
            }
            else
            {
                result.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, null);
            }

            var excel = Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "GarmentOmzet")
            }, true);

            return(excel);
        }
예제 #45
0
        public MemoryStream GenerateExcel(int unitId, long vbRequestId, string applicantName, string clearanceStatus, DateTimeOffset?requestDateFrom, DateTimeOffset?requestDateTo, DateTimeOffset?realizeDateFrom, DateTimeOffset?realizeDateTo, DateTimeOffset?clearanceDateFrom, DateTimeOffset?clearanceDateTo, int offSet)
        {
            var data = NewGetReportQuery(unitId, vbRequestId, applicantName, clearanceStatus, requestDateFrom, requestDateTo, realizeDateFrom, realizeDateTo, clearanceDateFrom, clearanceDateTo, offSet);

            var currencyGroup = data
                                .GroupBy(element => element.CurrencyCode)
                                .Select(group => new VBStatusByCurrencyReportViewModel()
            {
                CurrencyCode = group.Key,
                Total        = group.Sum(element => element.Amount)
            }).ToList();

            var dt = new DataTable();

            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No VB", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal VB", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Estimasi Tgl Realisasi", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Unit", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Pemohon VB", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Approval", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No Realisasi", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Tgl Realisasi", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Keperluan VB", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Aging (Hari)", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Mata Uang", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Jumlah VB", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Realisasi", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Sisa (Kurang/Lebih)", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Clearance", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Status", DataType = typeof(string)
            });

            var dtCurrency = new DataTable();

            dtCurrency.Columns.Add(new DataColumn()
            {
                ColumnName = "Mata Uang", DataType = typeof(string)
            });
            dtCurrency.Columns.Add(new DataColumn()
            {
                ColumnName = "Total", DataType = typeof(string)
            });

            var requestTotal     = 0.0;
            var realizationTotal = 0.0;

            if (data.Count == 0)
            {
                dt.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "");
            }
            else
            {
                data = data.OrderByDescending(s => s.LastModifiedUtc).ToList();

                foreach (var item in data)
                {
                    dt.Rows.Add(item.VBNo, item.Date, item.DateEstimate, item.Unit.Name, item.CreateBy, item.ApprovalDate, item.RealizationNo, item.RealizationDate, item.Usage, item.Aging, item.CurrencyCode,
                                item.Amount.ToString("#,##0.###0"), item.RealizationAmount.ToString("#,##0.###0"), item.Difference.ToString("#,##0.###0"), item.ClearenceDate, item.Status);
                }

                requestTotal     = (double)data.Sum(element => element.Amount);
                realizationTotal = (double)data.Sum(element => element.RealizationAmount);
            }

            if (currencyGroup.Count == 0)
            {
                dtCurrency.Rows.Add("", "");
            }
            else
            {
                currencyGroup = currencyGroup.OrderBy(element => element.CurrencyCode).ToList();
                foreach (var item in currencyGroup)
                {
                    dtCurrency.Rows.Add(item.CurrencyCode, item.Total.ToString("#,##0.###0"));
                }
            }

            return(Excel.CreateExcelVBStatusReport(new KeyValuePair <DataTable, string>(dt, "Status VB"), new KeyValuePair <DataTable, string>(dtCurrency, "MataUang"), requestDateFrom.GetValueOrDefault(), requestDateTo.GetValueOrDefault(), true, requestTotal, realizationTotal));
        }
예제 #46
0
        public string ExportExcelForStudents(Board board)
        {
            var fileName = board.Group.GroupName + "_" + board.BoardId + "_resultForStudents" + @".xlsx";

            var formFolderPath = Path.Combine(host.ContentRootPath, "forms");

            if (!System.IO.Directory.Exists(formFolderPath))
            {
                System.IO.Directory.CreateDirectory(formFolderPath);
            }

            var formFilePath = Path.Combine(formFolderPath, @"result_form.xlsx");
            // FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));

            var uploadFolderPath = Path.Combine(host.ContentRootPath, "exports/excel");

            if (!System.IO.Directory.Exists(uploadFolderPath))
            {
                System.IO.Directory.CreateDirectory(uploadFolderPath);
            }

            var filePath = Path.Combine(uploadFolderPath, fileName);

            //copy file from formfolder to export folder
            System.IO.File.Copy(formFilePath, filePath, true);
            FileInfo file = new FileInfo(Path.Combine(uploadFolderPath, fileName));

            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelWorksheet worksheet   = package.Workbook.Worksheets[1];
                int            studentRows = board.Group.Enrollments.Count();
                worksheet.Cells[8, 3].Value = board.Group.Project.Title;

                int row          = 12;
                var studentNames = board.Group.Enrollments.Select(e => e.Student.Name).ToList();
                foreach (var studentName in studentNames)
                {
                    worksheet.Cells[row, 3].Value = studentName;
                    row++;
                }

                row = 21;
                var lecturerInformations = board.BoardEnrollments.ToList();
                foreach (var lecturerInformation in lecturerInformations)
                {
                    worksheet.Cells[row, 2].Value = lecturerInformation.Lecturer.Name;
                    worksheet.Cells[row, 3].Value = lecturerInformation.BoardRole.BoardRoleName;
                    worksheet.Cells[row, 4].Value = lecturerInformation.Percentage;
                    worksheet.Cells[row, 5].Value = lecturerInformation.Comment;
                    worksheet.Cells[row, 6].Value = lecturerInformation.Score;
                    row++;
                }

                if (board.ResultScore != null)
                {
                    worksheet.Cells[25, 6].Value = board.ResultScore;
                    worksheet.Cells[26, 6].Value = board.ResultGrade;
                }

                worksheet.PrinterSettings.FitToPage   = true;
                worksheet.PrinterSettings.FitToWidth  = 1;
                worksheet.PrinterSettings.FitToHeight = 0;
                package.Save();

                //add to db
                var excel = new Excel {
                    FileName = fileName
                };
                excelRepository.AddExcel(excel);

                //send mail
                //SendMail(board, filePath);
                return(filePath);
            }
        }
예제 #47
0
파일: GcRangeGroup.cs 프로젝트: Daoting/dt
 public GcRangeGroup(Excel p_excel)
     : base(p_excel)
 {
     _groupLineInfos = new List <GroupLineInfo>();
     _groupDotInfos  = new List <GroupDotInfo>();
 }
예제 #48
0
 partial void UpdateExcel(Excel instance);
예제 #49
0
        public string ExportExcelForLecturers(Board board, BoardEnrollment boardEnrollment)
        {
            var fileName = board.Group.GroupName + "_" + board.BoardId + "_resultForLecturers" + @".xlsx";

            var formFolderPath = Path.Combine(host.ContentRootPath, "forms");

            if (!System.IO.Directory.Exists(formFolderPath))
            {
                System.IO.Directory.CreateDirectory(formFolderPath);
            }

            if (!board.Group.Project.Type.Equals("Final Project"))
            {
                var formFilePath = Path.Combine(formFolderPath, @"lecturer_form.xlsx");
                // FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));

                var uploadFolderPath = Path.Combine(host.ContentRootPath, "exports/excel");
                if (!System.IO.Directory.Exists(uploadFolderPath))
                {
                    System.IO.Directory.CreateDirectory(uploadFolderPath);
                }

                var filePath = Path.Combine(uploadFolderPath, fileName);

                //copy file from formfolder to export folder
                System.IO.File.Copy(formFilePath, filePath, true);
                FileInfo file = new FileInfo(Path.Combine(uploadFolderPath, fileName));

                using (ExcelPackage package = new ExcelPackage(file))
                {
                    ExcelWorksheet worksheet   = package.Workbook.Worksheets[1];
                    int            studentRows = board.Group.Enrollments.Count();
                    worksheet.Cells[8, 3].Value = board.Group.Project.Title;

                    int row          = 12;
                    var studentNames = board.Group.Enrollments.Select(e => e.Student.Name).ToList();
                    foreach (var studentName in studentNames)
                    {
                        worksheet.Cells[row, 3].Value = studentName;
                        row++;
                    }

                    worksheet.Cells[16, 3].Value = boardEnrollment.Lecturer.Name;

                    //add grade
                    var firstGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Trình bày tốt (Chuẩn bị slide tốt, trình bày rõ ràng đúng thời hạn)"));
                    worksheet.Cells[23, 4].Value = firstGrade.Score;
                    worksheet.Cells[23, 5].Value = firstGrade.Comment;

                    var secondGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Nội dung đề tai đạt yêu cầu đặt ra, có tính khoa học"));
                    worksheet.Cells[24, 4].Value = secondGrade.Score;
                    worksheet.Cells[24, 5].Value = secondGrade.Comment;

                    var thirdGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Phương pháp thực hiện tốt"));
                    worksheet.Cells[25, 4].Value = thirdGrade.Score;
                    worksheet.Cells[25, 5].Value = thirdGrade.Comment;

                    var fourthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Kết quả đề tài có áp dụng thực tế"));
                    worksheet.Cells[26, 4].Value = fourthGrade.Score;
                    worksheet.Cells[26, 5].Value = fourthGrade.Comment;

                    var fifthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Đề tài mới hoặc phương pháp thực hiện có tính sáng tạo"));
                    worksheet.Cells[27, 4].Value = fifthGrade.Score;
                    worksheet.Cells[27, 5].Value = fifthGrade.Comment;

                    var sixthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Trả lời tập trung vào đề tài, trả lời tốt câu hỏi"));
                    worksheet.Cells[29, 4].Value = sixthGrade.Score;
                    worksheet.Cells[29, 5].Value = sixthGrade.Comment;

                    worksheet.Cells[30, 4].Value = firstGrade.Score.Value + secondGrade.Score.Value + thirdGrade.Score.Value + fourthGrade.Score.Value
                                                   + fifthGrade.Score.Value + sixthGrade.Score.Value;
                    // worksheet.Cells[22, 2].Value = boardEnrollment.Score;
                    // worksheet.Cells[22, 3].Value = boardEnrollment.Comment;
                    // row = 22;
                    // foreach (var recommendation in boardEnrollment.Recommendations)
                    // {
                    //     worksheet.Cells[row, 5].Value = recommendation.Description;
                    //     row++;
                    // }

                    worksheet.Cells[32, 5].Value = "Ngày " + DateTime.Now.Day.ToString()
                                                   + " Tháng " + DateTime.Now.Month.ToString() + " Năm " + DateTime.Now.Year.ToString();

                    worksheet.PrinterSettings.FitToPage   = true;
                    worksheet.PrinterSettings.FitToWidth  = 1;
                    worksheet.PrinterSettings.FitToHeight = 0;
                    package.Save();

                    //add to db
                    var excel = new Excel {
                        FileName = fileName
                    };
                    excelRepository.AddExcel(excel);

                    //send mail
                    //SendMail(board, filePath);
                    return(filePath);
                }
            }
            else
            {
                var formFilePath = Path.Combine(formFolderPath, @"lecturer_final_form.xlsx");
                // FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));

                var uploadFolderPath = Path.Combine(host.ContentRootPath, "exports/excel");
                if (!System.IO.Directory.Exists(uploadFolderPath))
                {
                    System.IO.Directory.CreateDirectory(uploadFolderPath);
                }

                var filePath = Path.Combine(uploadFolderPath, fileName);

                //copy file from formfolder to export folder
                System.IO.File.Copy(formFilePath, filePath, true);
                FileInfo file = new FileInfo(Path.Combine(uploadFolderPath, fileName));

                using (ExcelPackage package = new ExcelPackage(file))
                {
                    ExcelWorksheet worksheet   = package.Workbook.Worksheets[1];
                    int            studentRows = board.Group.Enrollments.Count();
                    worksheet.Cells[8, 3].Value = board.Group.Project.Title;

                    int row          = 12;
                    var studentNames = board.Group.Enrollments.Select(e => e.Student.Name).ToList();
                    foreach (var studentName in studentNames)
                    {
                        worksheet.Cells[row, 3].Value = studentName;
                        row++;
                    }

                    worksheet.Cells[16, 3].Value = boardEnrollment.Lecturer.Name;

                    //add grade
                    var firstGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Introduction"));
                    worksheet.Cells[23, FindNumberOfCell(firstGrade.Score.Value)].Value = "X";
                    var a = FindNumberOfCell(firstGrade.Score.Value);
                    worksheet.Cells[23, 15].Value = firstGrade.Comment;

                    var secondGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Content (methods)"));
                    worksheet.Cells[24, FindNumberOfCell(secondGrade.Score.Value)].Value = "X";
                    worksheet.Cells[24, 15].Value = secondGrade.Comment;

                    var thirdGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Content (analysis)"));
                    worksheet.Cells[25, FindNumberOfCell(thirdGrade.Score.Value)].Value = "X";
                    worksheet.Cells[25, 15].Value = thirdGrade.Comment;

                    var fourthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Media and Visual aids"));
                    worksheet.Cells[26, FindNumberOfCell(fourthGrade.Score.Value)].Value = "X";
                    worksheet.Cells[26, 15].Value = fourthGrade.Comment;

                    var fifthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Presentation style (delivery)"));
                    worksheet.Cells[27, FindNumberOfCell(fifthGrade.Score.Value)].Value = "X";
                    worksheet.Cells[27, 15].Value = fifthGrade.Comment;

                    var sixthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Presentation style (preparation)"));
                    worksheet.Cells[28, FindNumberOfCell(sixthGrade.Score.Value)].Value = "X";
                    worksheet.Cells[28, 15].Value = sixthGrade.Comment;

                    var seventhGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Presentation style (style)"));
                    worksheet.Cells[29, FindNumberOfCell(seventhGrade.Score.Value)].Value = "X";
                    worksheet.Cells[29, 15].Value = sixthGrade.Comment;

                    var eighthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Audience connection"));
                    worksheet.Cells[30, FindNumberOfCell(eighthGrade.Score.Value)].Value = "X";
                    worksheet.Cells[30, 15].Value = sixthGrade.Comment;

                    var ninethGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Content (conclusion)"));
                    worksheet.Cells[31, FindNumberOfCell(ninethGrade.Score.Value)].Value = "X";
                    worksheet.Cells[31, 15].Value = sixthGrade.Comment;

                    var tenthGrade = boardEnrollment.Grades.FirstOrDefault(g => g.GradeDescription.Equals("Questions and Answers"));
                    worksheet.Cells[32, FindNumberOfCell(tenthGrade.Score.Value)].Value = "X";
                    worksheet.Cells[32, 15].Value = sixthGrade.Comment;

                    worksheet.Cells[34, 4].Value = firstGrade.Score.Value + secondGrade.Score.Value + thirdGrade.Score.Value + fourthGrade.Score.Value
                                                   + fifthGrade.Score.Value + sixthGrade.Score.Value + seventhGrade.Score.Value + eighthGrade.Score.Value
                                                   + ninethGrade.Score.Value + tenthGrade.Score.Value;
                    // worksheet.Cells[22, 2].Value = boardEnrollment.Score;
                    // worksheet.Cells[22, 3].Value = boardEnrollment.Comment;
                    // row = 22;
                    // foreach (var recommendation in boardEnrollment.Recommendations)
                    // {
                    //     worksheet.Cells[row, 5].Value = recommendation.Description;
                    //     row++;
                    // }

                    worksheet.Cells[36, 12].Value = "Ngày " + DateTime.Now.Day.ToString()
                                                    + " Tháng " + DateTime.Now.Month.ToString() + " Năm " + DateTime.Now.Year.ToString();

                    worksheet.PrinterSettings.FitToPage   = true;
                    worksheet.PrinterSettings.FitToWidth  = 1;
                    worksheet.PrinterSettings.FitToHeight = 0;
                    package.Save();

                    //add to db
                    var excel = new Excel {
                        FileName = fileName
                    };
                    excelRepository.AddExcel(excel);

                    //send mail
                    //SendMail(board, filePath);
                    return(filePath);
                }
            }
        }
예제 #50
0
        private void IDListView_SelectChange(object sender, SelectionChangedEventArgs e)
        {
            IDListItem item = (sender as ListView).SelectedItem as IDListItem;

            if (item == null)
            {
                return;
            }

            Excel excel = GlobalCfg.Instance.GetParsedExcel(_listItemChoosed.FilePath);
            List <PropertyInfo> propertyList = excel.Properties;
            ObservableCollection <PropertyListItem> fieldList = new ObservableCollection <PropertyListItem>();

            _IDItemSelected = item;
            List <lparser.config> configs = GlobalCfg.Instance.GetTableRow(item.ID);
            string ename = string.Empty;

            for (int i = 0; i < propertyList.Count; i++)
            {
                ename = propertyList[i].ename;
                fieldList.Add(new PropertyListItem()
                {
                    PropertyName = propertyList[i].cname,
                    EnName       = propertyList[i].ename,
                    Context      = configs[0] != null && configs[0].propertiesDic.ContainsKey(ename) ? configs[0].propertiesDic[ename].value : null,
                    Trunk        = configs[1] != null && configs[1].propertiesDic.ContainsKey(ename) ? configs[1].propertiesDic[ename].value : null,
                    Studio       = configs[2] != null && configs[2].propertiesDic.ContainsKey(ename) ? configs[2].propertiesDic[ename].value : null,
                    TF           = configs[3] != null && configs[3].propertiesDic.ContainsKey(ename) ? configs[3].propertiesDic[ename].value : null,
                    Release      = configs[4] != null && configs[4].propertiesDic.ContainsKey(ename) ? configs[4].propertiesDic[ename].value : null
                });
            }
            propertyDataGrid.ItemsSource = fieldList;
            ResetGenBtnState();

            //刷新单元格颜色
            for (int j = 0; j < GlobalCfg.BranchCount; j++)
            {
                tablerowdiff trd = GlobalCfg.Instance.GetCellAllStatus(item.ID, j);
                if (trd == null)
                {
                    continue;
                }
                for (int a = 0; a < fieldList.Count; a++)
                {
                    if (trd.modifiedcells != null && trd.modifiedcells.ContainsKey(fieldList[a].EnName))
                    {
                        DataGridCell dataGridCell = GetCell(propertyDataGrid, a, j + 3);
                        dataGridCell.Background = Brushes.LightBlue;
                    }
                    if (trd.modifiedcells != null && trd.addedcells.ContainsKey(fieldList[a].EnName))
                    {
                        DataGridCell dataGridCell = GetCell(propertyDataGrid, a, j + 3);
                        dataGridCell.Background = Brushes.LightPink;
                    }
                    if (trd.modifiedcells != null && trd.deletedcells.ContainsKey(fieldList[a].EnName))
                    {
                        DataGridCell dataGridCell = GetCell(propertyDataGrid, a, j + 3);
                        dataGridCell.Background = Brushes.LightBlue;
                    }
                }
            }
        }
예제 #51
0
 partial void InsertExcel(Excel instance);
예제 #52
0
        public MemoryStream GenerateExcel(int year, int month, int accountingBookId, string accountingBookType, bool valas)
        {
            string title = "LAPORAN DATA MEMORIAL",
                   date  = new DateTime(year, month, 1).ToString("MMMM yyyy", new CultureInfo("id-ID"));
            var query    = GetReportXlsData(year, month, accountingBookId, accountingBookType, valas);

            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nomor Memo", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Memo", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No. Akun", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nama Perkiraan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Keterangan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Debit", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kredit", DataType = typeof(String)
            });

            int index = 0;

            if (query.Data.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", 0.ToString("#,##0.#0"), 0.ToString("#,##0.#0")); // to allow column name to be generated properly for empty data as template
                index++;
            }
            else
            {
                foreach (var item in query.Data)
                {
                    index++;
                    result.Rows.Add(item.MemoGarmentPurchasing.MemoNo, item.MemoGarmentPurchasing.MemoDate.ToString("dd-MMM-yyyy", new CultureInfo("id-ID")), item.COANo, item.COAName, item.MemoGarmentPurchasing.Remarks, item.DebitNominal.ToString("#,##0.#0"), item.CreditNominal.ToString("#,##0.#0"));
                }
            }
            if (string.IsNullOrEmpty(accountingBookType))
            {
                return(Excel.CreateExcelWithTitleNonDateFilter(new List <KeyValuePair <DataTable, string> >()
                {
                    new KeyValuePair <DataTable, string>(result, "Memorial")
                }, title, date, true, 10));
            }
            else
            {
                return(Excel.CreateExcelWithTitleNonDateFilterMemoReport(new List <KeyValuePair <DataTable, string> >()
                {
                    new KeyValuePair <DataTable, string>(result, "Memorial")
                }, title, date, accountingBookType, true, 10));
            }
        }
예제 #53
0
 partial void DeleteExcel(Excel instance);
        public MemoryStream GenerateExcelTotalGarmentPurchaseBySupplier(string unit, bool jnsSpl, string payMtd, string category, DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var       Query  = GetTotalGarmentPurchaseBySupplierReportQuery(unit, jnsSpl, payMtd, category, dateFrom, dateTo, offset);
            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nomor", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Supplier", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Unit", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Kategori", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Metode Bayar", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Jumlah", DataType = typeof(Decimal)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Satuan", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Jumlah Konversi", DataType = typeof(Decimal)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Satuan Kecil", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nominal(Rp)", DataType = typeof(Decimal)
            });

            if (Query.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", "", 0, "", 0, "", 0); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                int index = 0;
                foreach (var item in Query)
                {
                    index++;
                    result.Rows.Add(index, item.SupplierName, item.UnitName, item.CategoryName, item.PaymentMethod, item.Quantity, item.UOMUnit, item.SmallQty, item.SmallUom, (Decimal)Math.Round((item.AmountIDR), 2));
                }
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Sheet1")
            }, true));
        }
예제 #55
0
 private bool HasSheetJudoka(int rowNumber, Excel.Worksheet worksheet)
 {
     String cell = GetJudokaCell(rowNumber, "B");
     String name = GetSheetValue(worksheet, cell);
     return name.CompareTo("") != 0;
 }
        public MemoryStream GenerateExcel(DateTimeOffset?dateFrom, DateTimeOffset?dateTo, int offSet)
        {
            //var query = _repository.ReadAll()
            //    .Where(s => s.Area == INSPECTIONMATERIAL && s.DyeingPrintingAreaInputProductionOrders.Any(d => !d.HasOutputDocument));
            var query = _repository.ReadAll().Where(s => s.Area == DyeingPrintingArea.INSPECTIONMATERIAL);

            if (dateFrom.HasValue && dateTo.HasValue)
            {
                query = query.Where(s => dateFrom.Value.Date <= s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date&&
                                    s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date <= dateTo.Value.Date);
            }
            else if (!dateFrom.HasValue && dateTo.HasValue)
            {
                query = query.Where(s => s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date <= dateTo.Value.Date);
            }
            else if (dateFrom.HasValue && !dateTo.HasValue)
            {
                query = query.Where(s => dateFrom.Value.Date <= s.Date.ToOffset(new TimeSpan(offSet, 0, 0)).Date);
            }


            query = query.OrderBy(s => s.BonNo);

            DataTable dt = new DataTable();

            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No. Bon", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No. SPP", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal Masuk", DataType = typeof(string)
            });

            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Qty Order", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No. Kereta", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Material", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Unit", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Buyer", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Warna", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Motif", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Satuan", DataType = typeof(string)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Qty Terima", DataType = typeof(string)
            });

            if (query.Count() == 0)
            {
                dt.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "");
            }
            else
            {
                foreach (var model in query)
                {
                    //foreach (var item in model.DyeingPrintingAreaInputProductionOrders.Where(d => !d.HasOutputDocument).OrderBy(s => s.ProductionOrderNo))
                    foreach (var item in model.DyeingPrintingAreaInputProductionOrders.OrderBy(s => s.ProductionOrderNo))
                    {
                        var dateIn = item.DateIn.Equals(DateTimeOffset.MinValue) ? "" : item.DateIn.ToOffset(new TimeSpan(offSet, 0, 0)).Date.ToString("d");

                        dt.Rows.Add(model.BonNo, item.ProductionOrderNo, dateIn, item.ProductionOrderOrderQuantity.ToString("N2", CultureInfo.InvariantCulture),
                                    item.CartNo, item.Construction, item.Unit, item.Buyer, item.Color, item.Motif, item.UomUnit, item.InputQuantity.ToString("N2", CultureInfo.InvariantCulture));
                    }
                }
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(dt, "Inspection Material")
            }, true));
        }
예제 #57
0
        /// <summary>
        /// 将指定索引列的数据相同的行合并,对指定WorkSheet操作
        /// </summary>
        /// <param name="columnIndex">要合并的列索引</param>
        /// <param name="beginRowIndex">合并开始行索引</param>
        /// <param name="rows">要合并的行数</param>
        private void MergeRows(Excel.Worksheet sheet, int columnIndex, int beginRowIndex, int rows)
        {
            int beginIndex = beginRowIndex;
            int count = 0;
            string text1;
            string text2;

            if (sheet == null)
                return;

            for (int j = beginRowIndex; j < beginRowIndex + rows; j++)
            {
                range1 = (Excel.Range)sheet.Cells[j, columnIndex];
                range2 = (Excel.Range)sheet.Cells[j + 1, columnIndex];
                text1 = range1.Text.ToString();
                text2 = range2.Text.ToString();

                if (text1 == text2)
                {
                    ++count;
                }
                else
                {
                    if (count > 0)
                    {
                        this.MergeCells(sheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
                    }

                    beginIndex = j + 1;		//设置开始合并行索引
                    count = 0;		//计数器清0
                }

            }

        }
        public MemoryStream GenerateExcel(string buyerAgent, string invoiceNo, string paymentTerm, DateTime?dateFrom, DateTime?dateTo, int offset)
        {
            var       Query  = GetData(buyerAgent, invoiceNo, paymentTerm, dateFrom, dateTo, offset);
            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No Invoice", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tgl Invoice", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tgl Payment", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tgl Kirim Dokumen", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Payment Term", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Buyer Name", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Buyer Address", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Bank Name", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Amount", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Amount To Be Paid", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Paid Amount", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Balance Amount", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nett Nego | TT", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Bank Charges | TT", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Other Charges | TT", DataType = typeof(string)
            });

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No LC | LC", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "No SR | LC", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Tanggal SR | LC", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Nett Nego | LC", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Bank Comission | LC", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Discreapancy Fee | LC", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Credit Interest | LC", DataType = typeof(string)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "Bank Charges | LC", DataType = typeof(string)
            });

            if (Query.ToArray().Count() == 0)
            {
                result.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "");
            }
            else
            {
                Dictionary <string, List <GarmentCreditAdviceMonitoringViewModel> > dataByBrand = new Dictionary <string, List <GarmentCreditAdviceMonitoringViewModel> >();
                Dictionary <string, double> subTotalAMT    = new Dictionary <string, double>();
                Dictionary <string, double> subTotalTBP    = new Dictionary <string, double>();
                Dictionary <string, double> subTotalNett   = new Dictionary <string, double>();
                Dictionary <string, double> subTotalNettTT = new Dictionary <string, double>();
                Dictionary <string, double> subTotalNettLC = new Dictionary <string, double>();
                Dictionary <string, double> outStanding    = new Dictionary <string, double>();

                foreach (GarmentCreditAdviceMonitoringViewModel item in Query.ToList())
                {
                    string BrandName = item.InvoiceNo;
                    if (!dataByBrand.ContainsKey(BrandName))
                    {
                        dataByBrand.Add(BrandName, new List <GarmentCreditAdviceMonitoringViewModel> {
                        });
                    }
                    dataByBrand[BrandName].Add(new GarmentCreditAdviceMonitoringViewModel
                    {
                        InvoiceNo     = item.InvoiceNo,
                        InvoiceDate   = item.InvoiceDate,
                        PaymentDate   = item.PaymentDate,
                        DocUploadDate = item.DocUploadDate,
                        PaymentTerm   = item.PaymentTerm,
                        Amount        = item.Amount,
                        ToBePaid      = item.ToBePaid,
                        PaidAmount    = item.PaidAmount,
                        BalanceAmount = item.BalanceAmount,
                        BuyerName     = item.BuyerName,
                        BuyerAddress  = item.BuyerAddress,
                        BankName      = item.BankName,
                        NettNego      = item.NettNego,
                        NettNegoTT    = item.NettNegoTT,
                        BankChargeTT  = item.BankChargeTT,
                        OtherChargeTT = item.OtherChargeTT,

                        SRNo              = item.SRNo,
                        SRDate            = item.SRDate,
                        LCNo              = item.LCNo,
                        NettNegoLC        = item.NettNegoLC,
                        BankChargeLC      = item.BankChargeLC,
                        BankComissionLC   = item.BankComissionLC,
                        DiscreapancyFeeLC = item.DiscreapancyFeeLC,
                        CreditInterestLC  = item.CreditInterestLC,
                    });

                    if (!subTotalNett.ContainsKey(BrandName))
                    {
                        subTotalNett.Add(BrandName, 0);
                    }
                    ;

                    if (!subTotalNettTT.ContainsKey(BrandName))
                    {
                        subTotalNettTT.Add(BrandName, 0);
                    }
                    ;

                    if (!subTotalNettLC.ContainsKey(BrandName))
                    {
                        subTotalNettLC.Add(BrandName, 0);
                    }
                    ;

                    subTotalAMT[BrandName]     = item.Amount;
                    subTotalTBP[BrandName]     = item.ToBePaid;
                    subTotalNett[BrandName]   += item.NettNego;
                    subTotalNettTT[BrandName] += item.NettNegoTT;
                    subTotalNettLC[BrandName] += item.NettNegoLC;

                    outStanding[BrandName] = subTotalTBP[BrandName] - subTotalNett[BrandName];
                }

                int rowPosition = 1;
                foreach (KeyValuePair <string, List <GarmentCreditAdviceMonitoringViewModel> > BuyerBrand in dataByBrand)
                {
                    string BrandCode = "";
                    int    index     = 0;
                    foreach (GarmentCreditAdviceMonitoringViewModel item in BuyerBrand.Value)
                    {
                        index++;

                        string InvDate = item.InvoiceDate == new DateTime(1970, 1, 1) ? "-" : item.InvoiceDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));
                        string DocDate = item.DocUploadDate == new DateTime(1970, 1, 1) ? "-" : item.DocUploadDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));
                        string SRDate  = item.SRDate == new DateTime(1970, 1, 1) ? "-" : item.SRDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));
                        string PayDate = item.PaymentDate == new DateTime(1970, 1, 1) ? "-" : item.PaymentDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("MM/dd/yyyy", new CultureInfo("us-US"));

                        string AmtFOB  = string.Format("{0:N2}", item.Amount);
                        string AmtPaid = string.Format("{0:N2}", item.ToBePaid);
                        string PaidAmt = string.Format("{0:N2}", item.PaidAmount);
                        string BlncAmt = string.Format("{0:N2}", item.BalanceAmount);

                        string NettTT  = string.Format("{0:N2}", item.Amount);
                        string BChrgTT = string.Format("{0:N2}", item.ToBePaid);
                        string OChrgTT = string.Format("{0:N2}", item.Amount);

                        string CommLC  = string.Format("{0:N2}", item.ToBePaid);
                        string FeeLC   = string.Format("{0:N2}", item.Amount);
                        string NettLC  = string.Format("{0:N2}", item.ToBePaid);
                        string IntLC   = string.Format("{0:N2}", item.Amount);
                        string BChrgLC = string.Format("{0:N2}", item.ToBePaid);

                        result.Rows.Add(index, item.InvoiceNo, InvDate, PayDate, DocDate, item.PaymentTerm, item.BuyerName, item.BuyerAddress, item.BankName,
                                        AmtFOB, AmtPaid, PaidAmt, BlncAmt, NettTT, BChrgTT, OChrgTT, item.LCNo, item.SRNo, SRDate, CommLC, FeeLC, NettLC, IntLC, BChrgLC);

                        rowPosition += 1;
                        BrandCode    = item.InvoiceNo;
                    }

                    result.Rows.Add("", "", "INVOICE NO :", BrandCode, "", "", "AMOUNT :", Math.Round(subTotalAMT[BuyerBrand.Key], 2), "", "", "AMOUNT TO BE PAID :", Math.Round(subTotalTBP[BuyerBrand.Key], 2), "", "", "PAID AMOUNT :", Math.Round(subTotalNett[BuyerBrand.Key], 2), "", "", "OUTSTANDING AMOUNT :", Math.Round(outStanding[BuyerBrand.Key], 2), "");

                    rowPosition += 1;
                }
            }
            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Sheet1")
            }, true));
        }
예제 #59
0
 public ExcelParameter(System.Drawing.Font f, MExcel.XlHAlign Align, D.Point StartCell, D.Point EndCell, string value)
     : this(Excel.ColNumToEx(StartCell.X) + StartCell.Y.ToString(), Excel.ColNumToEx(EndCell.X) + EndCell.Y.ToString(), value, Align, f, null)
 {
 }
예제 #60
0
        public MemoryStream GenerateExcel(int kanbanID, int machineID, DateTime?dateFrom, DateTime?dateTo, int offSet)
        {
            var data = GetReport(kanbanID, machineID, dateFrom, dateTo, offSet);

            data = data.OrderByDescending(x => x.LastModifiedUtc).ToList();

            DataTable dt = new DataTable();

            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No Order", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "No Kereta", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Reproses", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Mesin", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Step Proses", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Material", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Warna", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Lebar Kain (inch)", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Jenis Proses", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Tgl Input", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Jam Input", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Input", DataType = typeof(Double)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Tgl Output", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Jam Output", DataType = typeof(String)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "BQ", DataType = typeof(Double)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "BS", DataType = typeof(Double)
            });
            dt.Columns.Add(new DataColumn()
            {
                ColumnName = "Keterangan BQ", DataType = typeof(String)
            });

            if (data.Count == 0)
            {
                dt.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", 0, "", "", 0, 0, "");
            }
            else
            {
                int index = 1;
                foreach (var item in data)
                {
                    dt.Rows.Add(index++, item.Kanban.ProductionOrder.OrderNo, item.Kanban.Cart.CartNumber, item.Kanban.IsReprocess.ToString(), item.Machine.Name,
                                item.Step.Process, item.Kanban.ProductionOrder.Material.Name, item.Kanban.SelectedProductionOrderDetail.ColorRequest, item.Kanban.FinishWidth,
                                item.Kanban.ProductionOrder.ProcessType.Name, item.DateInput == null ? "" : item.DateInput.GetValueOrDefault().AddHours(offSet).ToString("dd/MM/yyyy"),
                                item.TimeInput == null ? "" : item.TimeInput.GetValueOrDefault().ToString(), item.Input.GetValueOrDefault(),
                                item.DateOutput == null ? "" : item.DateOutput.GetValueOrDefault().AddHours(offSet).ToString("dd/MM/yyyy"), item.TimeOutput == null ? "" : item.TimeOutput.GetValueOrDefault().ToString(),
                                item.GoodOutput.GetValueOrDefault(), item.BadOutput.GetValueOrDefault(), "");
                }
            }

            return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(dt, "Daily Operation")
            }, true));
        }