Create invoices with winform app using spire.XLS!?

Post a reply
Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Sun Mar 07, 2021 6:09 am

Hi All,

I'm trying to create a invoice generation windows form application using C# and spire.XLS. I've attached the desired output result invoice (named
Desired_Output.xlsx
LOL :lol: ).

Basically I'm trying to get the items(i.e. Products names) that I select in the combo box and when I hit a button then that same item i.e. comboBox1.text gets copied to a specific cell/cells along with other info (Batch No, MFG & EXP date) in the next row/rows (image attached). Then when I select another item from the combo box then the above step happens but the data gets written in a new row and not overwrite the earlier entered data. (Hope when you see
Desired_Output.xlsx
you will get what I'm trying to say, english isn't my first language :oops: )

I've done the below code which generated the excel template for the invoice:
Code: Select all
      private void InvoiceTemplate()
      {
         Workbook workbook = new Workbook();
         Worksheet sheet = workbook.Worksheets[0];
         sheet.Name = "Test";
         
         sheet.PageSetup.TopMargin = 0.5;
         sheet.PageSetup.BottomMargin = 0.5;
         sheet.PageSetup.LeftMargin = 0.3;
         sheet.PageSetup.RightMargin = 0.3;

         sheet.PageSetup.HeaderMarginInch = 0.3;
         sheet.PageSetup.FooterMarginInch = 0.3;
         
         sheet.Range["J52:J53"].Borders[BordersLineType.EdgeLeft].LineStyle=LineStyleType.Thin;
         sheet.Range["J53:J53"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Double;
         sheet.Range["A2:A60"].Borders[BordersLineType.EdgeLeft].LineStyle=LineStyleType.Thin;
         sheet.Range["J2:J60"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["G2:G3"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["G12:G13"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["G22:G51"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["H22:H51"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["I22:I51"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["D2:D21"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["B15:B21"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["E3:J3"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["E5:J5"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["E7:J7"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["E11:J11"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["E13:J13"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["A21:J21"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["A7:D7"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["A14:D14"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["A16:D16"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["A18:D18"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["D2:D21"].Borders[BordersLineType.EdgeRight].LineStyle=LineStyleType.Thin;
         sheet.Range["A52:J52"].Borders[BordersLineType.EdgeTop].LineStyle=LineStyleType.Thin;
         sheet.Range["A60:J60"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         sheet.Range["G55:J55"].Borders[BordersLineType.EdgeTop].LineStyle=LineStyleType.Thin;
         sheet.Range["G55:G60"].Borders[BordersLineType.EdgeLeft].LineStyle=LineStyleType.Thin;
         
         sheet.Range["J52:J53"].Merge();
         
         sheet.Range["A1:J1"].Merge();
         sheet.SetRowHeight(1,17.25);
         sheet.Range["A1"].Text = "INVOICE";
         sheet.Range["A1"].Style.Font.FontName="Cambria";
         sheet.Range["A1"].Style.Font.Size=11;
         sheet.Range["A1"].Style.Font.IsBold=true;
         sheet.Range["A1"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A1"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         sheet.Range["A1:J1"].Borders[BordersLineType.EdgeBottom].LineStyle=LineStyleType.Thin;
         
         sheet.Range["A3:D3"].Merge();
         sheet.Range["A3"].Text = "RALSONS DORMA PRIVATE LIMITED";
         sheet.Range["A3"].Style.Font.FontName="Cambria";
         sheet.Range["A3"].Style.Font.Size=10;
         sheet.Range["A3"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A3"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["A4:D7"].Merge();
         sheet.Range["A4"].Text = "31/S/7, YO-HIC-SINH PARANI,\r\nSILPARA - 700 031, INDIA\r\nSTATE CODE:-19;  GSTIN NO. 99PAECL1620T1OD\r\nTEL : 00 91 44 2585 4270, FAX : 00 91 44 2585 4520";
         sheet.Range["A4"].Style.Font.FontName="Cambria";
         sheet.Range["A4"].Style.Font.Size=9;
         sheet.Range["A4"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A4"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["A2:D2"].Merge();
         sheet.Range["A2"].Text = "Exporter";
         sheet.Range["A2"].Style.Font.FontName="Cambria";
         sheet.Range["A2"].Style.Font.Size=10;
         sheet.Range["A2"].Style.Font.IsBold=true;
         sheet.Range["A2"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A2"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["E2:G2"].Merge();
         sheet.Range["E2"].Text = "Invoice No. & Date";
         sheet.Range["E2"].Style.Font.FontName="Cambria";
         sheet.Range["E2"].Style.Font.Size=10;
         sheet.Range["E2"].Style.Font.IsBold=true;
         sheet.Range["E2"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E2"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["H2:J2"].Merge();
         sheet.Range["H2"].Text = "Exporter's Ref.";
         sheet.Range["H2"].Style.Font.FontName="Cambria";
         sheet.Range["H2"].Style.Font.Size=10;
         sheet.Range["H2"].Style.Font.IsBold=true;
         sheet.Range["H2"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["H2"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["E4:J4"].Merge();
         sheet.Range["E4"].Text = "Buyer's Order No & Date";
         sheet.Range["E4"].Style.Font.FontName="Cambria";
         sheet.Range["E4"].Style.Font.Size=10;
         sheet.Range["E4"].Style.Font.IsBold=true;
         sheet.Range["E4"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E4"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["E6:J6"].Merge();
         sheet.Range["E6"].Text = "Other Reference(s)";
         sheet.Range["E6"].Style.Font.FontName="Cambria";
         sheet.Range["E6"].Style.Font.Size=10;
         sheet.Range["E6"].Style.Font.IsBold=true;
         sheet.Range["E6"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E6"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["A8:D8"].Merge();
         sheet.Range["A8"].Text = "Consignee";
         sheet.Range["A8"].Style.Font.FontName="Cambria";
         sheet.Range["A8"].Style.Font.Size=10;
         sheet.Range["A8"].Style.Font.IsBold=true;
         sheet.Range["A8"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A8"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["E8:J8"].Merge();
         sheet.Range["E8"].Text = "Buyer (if other than consignee)";
         sheet.Range["E8"].Style.Font.FontName="Cambria";
         sheet.Range["E8"].Style.Font.Size=10;
         sheet.Range["E8"].Style.Font.IsBold=true;
         sheet.Range["E8"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E8"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["E12:G12"].Merge();
         sheet.Range["E12"].Text = "Country of Origin of Goods";
         sheet.Range["E12"].Style.Font.FontName="Cambria";
         sheet.Range["E12"].Style.Font.Size=10;
         sheet.Range["E12"].Style.Font.IsBold=true;
         sheet.Range["E12"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E12"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         
         sheet.Range["H12:J12"].Merge();
         sheet.Range["H12"].Text = "Country of Final Destination";
         sheet.Range["H12"].Style.Font.FontName="Cambria";
         sheet.Range["H12"].Style.Font.Size=10;
         sheet.Range["H12"].Style.Font.IsBold=true;
         sheet.Range["H12"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["H12"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         
         sheet.Range["E14:J14"].Merge();
         sheet.Range["E14"].Text = "Terms of Delivery and Payment";
         sheet.Range["E14"].Style.Font.FontName="Cambria";
         sheet.Range["E14"].Style.Font.Size=10;
         sheet.Range["E14"].Style.Font.IsBold=true;
         sheet.Range["E14"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E14"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["A15:B15"].Merge();
         sheet.Range["A15"].Text = "Pre Carriage by";
         sheet.Range["A15"].Style.Font.FontName="Cambria";
         sheet.Range["A15"].Style.Font.Size=10;
         sheet.Range["A15"].Style.Font.IsBold=true;
         sheet.Range["A15"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A15"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["C15:D16"].Merge();
         sheet.Range["C15"].Text = "Place of Receipt by Pre-Carrier";
         sheet.Range["C15"].Style.Font.FontName="Cambria";
         sheet.Range["C15"].Style.Font.Size=8;
         sheet.Range["C15"].Style.Font.IsBold=true;
         sheet.Range["C15"].Style.VerticalAlignment=VerticalAlignType.Top;
         sheet.Range["C15"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["A17:B17"].Merge();
         sheet.Range["A17"].Text = "Vessel / Flight No.";
         sheet.Range["A17"].Style.Font.FontName="Cambria";
         sheet.Range["A17"].Style.Font.Size=10;
         sheet.Range["A17"].Style.Font.IsBold=true;
         sheet.Range["A17"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A17"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["C17:D17"].Merge();
         sheet.Range["C17"].Text = "Port of Loading";
         sheet.Range["C17"].Style.Font.FontName="Cambria";
         sheet.Range["C17"].Style.Font.Size=10;
         sheet.Range["C17"].Style.Font.IsBold=true;
         sheet.Range["C17"].Style.VerticalAlignment=VerticalAlignType.Top;
         sheet.Range["C17"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["E18"].Text = "PAYMENT :";
         sheet.Range["E18"].Style.Font.FontName="Cambria";
         sheet.Range["E18"].Style.Font.Size=10;
         sheet.Range["E18"].Style.Font.IsBold=true;
         sheet.Range["E18"].Style.VerticalAlignment=VerticalAlignType.Top;
         sheet.Range["E18"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["A19:B19"].Merge();
         sheet.Range["A19"].Text = "Port of Discharge";
         sheet.Range["A19"].Style.Font.FontName="Cambria";
         sheet.Range["A19"].Style.Font.Size=10;
         sheet.Range["A19"].Style.Font.IsBold=true;
         sheet.Range["A19"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A19"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["C19:D19"].Merge();
         sheet.Range["C19"].Text = "Final Destination";
         sheet.Range["C19"].Style.Font.FontName="Cambria";
         sheet.Range["C19"].Style.Font.Size=10;
         sheet.Range["C19"].Style.Font.IsBold=true;
         sheet.Range["C19"].Style.VerticalAlignment=VerticalAlignType.Top;
         sheet.Range["C19"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["A20:B21"].Merge();
         sheet.Range["C20:D21"].Merge();
         sheet.Range["A22:B23"].Merge();
         sheet.Range["A22"].Text = "Marks & Nos./\r\nContainer No.";
         sheet.Range["A22"].Style.Font.FontName="Cambria";
         sheet.Range["A22"].Style.Font.Size=11;
         sheet.Range["A22"].Style.Font.IsBold=true;
         sheet.Range["A22"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A22"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         
         sheet.Range["C22:D23"].Merge();
         sheet.Range["C22"].Text = "No. & Kind of Pkgs.";
         sheet.Range["C22"].Style.Font.FontName="Cambria";
         sheet.Range["C22"].Style.Font.Size=11;
         sheet.Range["C22"].Style.Font.IsBold=true;
         sheet.Range["C22"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["C22"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["E22:G23"].Merge();
         sheet.Range["E22"].Text = "Description of Goods";
         sheet.Range["E22"].Style.Font.FontName="Cambria";
         sheet.Range["E22"].Style.Font.Size=11;
         sheet.Range["E22"].Style.Font.IsBold=true;
         sheet.Range["E22"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E22"].Style.HorizontalAlignment=HorizontalAlignType.Center;

         sheet.Range["E24:G24"].Merge();
         sheet.Range["E24"].Text = "SKIN CARE PRODUCTS";
         sheet.Range["E24"].Style.Font.FontName="Cambria";
         sheet.Range["E24"].Style.Font.Size=12;
         sheet.Range["E24"].Style.Font.IsBold=true;
         sheet.Range["E24"].Style.Font.Underline=FontUnderlineType.Single;
         sheet.Range["E24"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["E24"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         
         sheet.Range["H22:H23"].Merge();
         sheet.Range["H22"].Text = "Quantity";
         sheet.Range["H22"].Style.Font.FontName="Cambria";
         sheet.Range["H22"].Style.Font.Size=11;
         sheet.Range["H22"].Style.Font.IsBold=true;
         sheet.Range["H22"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["H22"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         
         sheet.Range["I22"].Text = "Rate";
         sheet.Range["I22"].Style.Font.FontName="Cambria";
         sheet.Range["I22"].Style.Font.Size=10;
         sheet.Range["I22"].Style.Font.IsBold=true;
         sheet.Range["I22"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["I22"].Style.HorizontalAlignment=HorizontalAlignType.Center;

         sheet.Range["J22"].Text = "Amount";
         sheet.Range["J22"].Style.Font.FontName="Cambria";
         sheet.Range["J22"].Style.Font.Size=10;
         sheet.Range["J22"].Style.Font.IsBold=true;
         sheet.Range["J22"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["J22"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         
         sheet.Range["I23:J23"].Merge();
         sheet.Range["I23"].Text="U.S.DOLLARS";
         sheet.Range["I23"].Style.Font.FontName="Cambria";
         sheet.Range["I23"].Style.Font.Size=10;
         sheet.Range["I23"].Style.Font.IsBold=true;
         sheet.Range["I23"].Style.Font.Underline=FontUnderlineType.Single;
         sheet.Range["I23"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["I23"].Style.HorizontalAlignment=HorizontalAlignType.Center;
         
         //LAST LINES
         sheet.Range["A52"].Text="Amount Chargeable";
         sheet.Range["A52"].Style.Font.FontName="Cambria";
         sheet.Range["A52"].Style.Font.Size=10;
         sheet.Range["A52"].Style.Font.IsBold=true;
         sheet.Range["A52"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A52"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["A53"].Text="(in words)";
         sheet.Range["A53"].Style.Font.FontName="Cambria";
         sheet.Range["A53"].Style.Font.Size=10;
         sheet.Range["A53"].Style.Font.IsBold=true;
         sheet.Range["A53"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A53"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["A54"].Text="I.E. CODE NO.0200008602";
         sheet.Range["A54"].Style.Font.FontName="Cambria";
         sheet.Range["A54"].Style.Font.Size=10;
         sheet.Range["A54"].Style.Font.IsBold=true;
         sheet.Range["A54"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A54"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["G55"].Text="Signature & Date";
         sheet.Range["G55"].Style.Font.FontName="Cambria";
         sheet.Range["G55"].Style.Font.Size=10;
         sheet.Range["G55"].Style.Font.IsBold=true;
         sheet.Range["G55"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["G55"].Style.HorizontalAlignment=HorizontalAlignType.Left;

         sheet.Range["A57"].Text="Declaration :";
         sheet.Range["A57"].Style.Font.FontName="Cambria";
         sheet.Range["A57"].Style.Font.Size=10;
         sheet.Range["A57"].Style.Font.IsBold=true;
         sheet.Range["A57"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A57"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["A58"].Text="We declare that this Invoice shows the actual price of the";
         sheet.Range["A58"].Style.Font.FontName="Cambria";
         sheet.Range["A58"].Style.Font.Size=10;
         sheet.Range["A58"].Style.Font.IsBold=true;
         sheet.Range["A58"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A58"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.Range["A59"].Text="goods described that all particulars are true and correct";
         sheet.Range["A59"].Style.Font.FontName="Cambria";
         sheet.Range["A59"].Style.Font.Size=10;
         sheet.Range["A59"].Style.Font.IsBold=true;
         sheet.Range["A59"].Style.VerticalAlignment=VerticalAlignType.Center;
         sheet.Range["A59"].Style.HorizontalAlignment=HorizontalAlignType.Left;
         
         sheet.SetColumnWidth(1,11.57);
         sheet.SetColumnWidth(2,7.57);
         sheet.SetColumnWidth(3,9.71);
         sheet.SetColumnWidth(4,12.43);
         sheet.SetColumnWidth(5,10.57);
         sheet.SetColumnWidth(6,7.29);
         sheet.SetColumnWidth(7,9.43);
         sheet.SetColumnWidth(8,7.57);
         sheet.SetColumnWidth(9,7);
         sheet.SetColumnWidth(10,11.72);
         
         workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
         System.Diagnostics.Process.Start("sample.xlsx");
         
      }


Also, the combo box has the following code at the form activated event:
Code: Select all
         Workbook workbook = new Workbook();
         workbook.LoadFromFile(@"D:\Jay\Proj\products.xlsx");
         Worksheet sheet = workbook.Worksheets[0];

         CellRange[] items = sheet.Columns[0].Cells;

         foreach (CellRange item in items)
         {
            if (!String.IsNullOrEmpty(item.Value2.ToString()))
            {
               comboBox1.Items.Add(item.Value);
            }
            
         }


But I'm struggling to get the combo box items to the excel file in such a way so that I can write the below data side by side :
"ABC PHARMA INC
MANILA"

1/247 TO 247/247 =
247 CARTONS
1/46 TO 46/ 46 =

46 CARTONS
TOTAL 293 CARTONS

and

293 NOS

EXPORT STANDARD CARTONS

in such a way shown in the desired output file.

Also, if the product name data are too many then the program needs to automatically create more pages of the invoice with the same header and footer rows (i.e. Range["A1:J21"] and Range["A52:J60"] should remain same).

Apologies again for the bad english.

Please help :(
Attachments
Test.zip
(476.55 KiB) Downloaded 419 times

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Mon Mar 08, 2021 11:29 am

Hello,

Thanks for your inquiry.
According to your description, I made a demo project for your better reference. Please give it a try on your side.
If this is not what you want, please provide us with more information. Thanks in advance.

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Mon Mar 08, 2021 1:17 pm

Hi Elena,

After downloading your project when I tried to open it I got an error.(Image attached).

So, could not check out the same. :(

I wish I could share my project but file size limit on this forum is stopping me from uploading and also I cannot provide a link either :(
Attachments
error.zip
(79.82 KiB) Downloaded 421 times

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Tue Mar 09, 2021 1:41 am

Hello,

Sorry for my mistake. I have corrected the project, please download it and try again. Any questions, just feel free to write back.
link: http://www.e-iceblue.com/downloads/demo/24633Demo.zip

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Tue Mar 09, 2021 1:38 pm

Hi,

Thanks for your reply. Your project is close but not exactly what I want.

I'm attaching here my project (you have to install the Spire.XLS package coz the file size becomes large to upload).
InvoiceGenerator.zip
(45.97 KiB) Downloaded 429 times


Hope this gives you a better idea of what I'm trying to achieve. :D

Basically when I select an item i.e. a Product name from combo box1 and then select a number 1,2 or 3 from combo box2 (the number in combo box2 indicated how many times I have to write the Batch No., DATE OF MFG. & USE BEFORE per Product name in the invoice) and hit the ADD button it gets added to the list boxes 1 & 2 respectively and do this process till I added all the Product names and then when I hit the generate button then the invoice excel is generated by adding those Products in the excel files middle portion like my expected output file and the text that I write in the text boxes gets written in the respective portions in the Range["A1:J21"].

Thanks in advance.

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Wed Mar 10, 2021 9:54 am

Hello,

Thanks for providing more information.
According to your description, I made some adjustments to your project. Please download it from the following link. Feel free to contact us if you have further questions.
link: http://www.e-iceblue.com/downloads/demo/24633Demo2.zip

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Wed Mar 10, 2021 2:47 pm

Hi,

Thanks for your continuous support.

I've run the modified project you provided and found some things. Firstly, when I'm selecting any item from any of the combo boxes it automatically gets added to the corresponding list boxes, that should happen when I hit the ADD button. But that's the small issue, I've added an attachment which contains the excel files generated by your code (Result-MergeCells.xlsx) and the output that I want(EXPECTED_RESULT.xlsx) along with video file for your better understanding.
TEST.zip
(1.41 MiB) Downloaded 429 times


Note that for the first product that I chose i.e. ACNE-GONE BAR 100GM the Batch No., DATE OF MFG. & USE BEFORE is appearing twice for the same product because I've chosen 2 in the 2nd combo box. Also, there is no need to create multiple pages in this case as all the products and their data can be filled up in the given default range if the data would have come down to row no. 52 in EXPECTED_RESULT.xlsx which is the fixed bottom portion of the invoice then a page should be created.

Here is another example
test2.zip
(86.04 KiB) Downloaded 422 times


Hope you get a little more idea about this now :)

Regards,
Jayanta

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Thu Mar 11, 2021 10:37 am

Hello,

Thanks for providing more information.
Attached is the modified project for your reference. Feel free to contact us if you have further questions.
InvoiceGenerator.zip
(222.6 KiB) Downloaded 426 times

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Thu Mar 11, 2021 2:10 pm

Hi,

Thanks for the modified project. But there are still few key problems here.

I've run your modified project as below:
test.zip
(54.71 KiB) Downloaded 438 times


This is what your code produced:
Result-MergeCells.zip
(11.54 KiB) Downloaded 427 times


And this is the required/expected output:
EXPECTED_RESULT.zip
(12.69 KiB) Downloaded 426 times


Hope this helps further!

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Fri Mar 12, 2021 9:53 am

Hello,

Thanks for your feedback.
Based on your more information, I did some adjustments. Please download the updated project from the following link. Feel free to contact us if you have further questions.
link: http://www.e-iceblue.com/downloads/demo ... erator.zip

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Fri Mar 12, 2021 1:34 pm

Hi,

There still seems to be a problem in the code. Your code seems to be generating extra pages with messed up data (marked in yellow in the result.xlsx [i.e. Result-MergeCells.xlsx, just renamed it in the code] file). Furthermore, in the first page there seems to a lot of blank rows left where there could have been data (i.e. Prouduct name[from list box1], Batch No., DATE OF MFG. & USE BEFORE). Check my EXPECTED_RESULT.xlsx file in the attachment. It is preferred to leave 3 blank rows before the row from the row having Amount Chargeable text but not more than that, in the file result.xlsx there seems to be 13 blank rows in the first page after row no.38.
Spire.XLS C#.zip
(84.75 KiB) Downloaded 423 times


Regards
Jayanta

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Mon Mar 15, 2021 10:33 am

Hello,

Thanks for your feedback.
Attached is my modified project for your reference. Feel free to contact us if you have further questions.
InvoiceGenerator.zip
(218.8 KiB) Downloaded 421 times

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Tue Mar 16, 2021 1:27 pm

Hi,

I can't thank you enough for the continuous support. :D

Your modified project is very close but there are still some issues. I think your method of calculating the rows where the data are written (taken from the list box items) and when to move to a new page need some change e.x. in my attached image file, for products E BABA SKIN CREAM 180 GM and ALUDOMM MOISTURISING LOTION 300 ML your code creates new pages and then writes the data from the start since (I think) it cannot fit all 3 Batch No., DATE OF MFG. & USE BEFORE in the same page so it moves to the next page but that should not be the case, check my EXPECTED_RESULT.xlsx in the attachment.

Also, there are some formatting issues in Page2 & 3 of our Result-MergeCells.xlsx.
Spire.XLS C#.zip
(243.67 KiB) Downloaded 404 times


Thanks in advance.

Jayanta

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Wed Mar 17, 2021 9:33 am

Hello,

Thanks for your feedback.
I have adjusted the code based on your description. Feel free to contact us if you have further questions.

InvoiceGenerator.zip
(260.22 KiB) Downloaded 416 times


Sincerely
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Thu Mar 18, 2021 1:43 am

Hi,

It seems that I'm still unable to properly explain to you when the data should move to the new page.

After the row where 'SKIN CARE PRODUCTS' is written in every page there are 27 rows before the row where 'Amount Chargeable', so all the products name and its related data(i.e. batch, mfg date & use before) needs to be written in that space keeping the last three rows blank i.e. there are 24 rows in each page where to write the above mentioned data.

Now, when a case like below arises

CASE 1:
-------
Code: Select all
listbox1     listbox2
ITEM1        3
ITEM2        3
ITEM3        3
ITEM2        1


Then ITEM1's data takes 9 rows and then followed by 1 blank row, so 10 rows out of 24 is used in 1st page
Then ITEM2's data takes 9 rows and then followed by 1 blank row, so 20 rows out of 24 is used in 1st page
Then ITEM3's data will take 9 rows, but only 4 rows are left to fill in the first page, so in this case from row 21 to 23 the code should write the below:

Code: Select all
ITEM3
Batch No.      
DATE OF MFG.      USE BEFORE


and then move to new a page i.e. page 2 and then write the below afer 'SKIN CARE PRODUCTS':

Code: Select all
ITEM3
Batch No.      
DATE OF MFG.      USE BEFORE

Batch No.      
DATE OF MFG.      USE BEFORE


(NOTE that the 'ITEM3' is also copied to this new page as to indicate whose data is continuing in page2) and the same logic will apply for the next pages as well (if required)



CASE 2:
-------
Code: Select all
listbox1     listbox2
ITEM1        3
ITEM2        2
ITEM3        3
ITEM2        1


Then ITEM1's data takes 9 rows and then followed by 1 blank row, so 10 rows out of 24 is used in 1st page
Then ITEM2's data takes 6 rows and then followed by 1 blank row, so 17 rows out of 24 is used in 1st page
Then ITEM3's data will take 9 rows, but only 7 rows are left to fill in the first page, so in this case from row 18 to 23 the code should write the below:

Code: Select all
ITEM3
Batch No.      
DATE OF MFG.      USE BEFORE

Batch No.      
DATE OF MFG.      USE BEFORE


and then move to new a page i.e. page 2 and then write the below afer 'SKIN CARE PRODUCTS':

Code: Select all
ITEM3
Batch No.      
DATE OF MFG.      USE BEFORE


(NOTE that the 'ITEM3' is also copied to this new page as to indicate whose data is continuing in page2) and the same logic will apply for the next pages as well (if required)

Hope this clarifies the logic.
Spire.XLS C#.zip
(178.04 KiB) Downloaded 423 times

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm
Post a reply

Return to Spire.XLS

Products
Products
Purchase
Partnership
Support
Corporation
Copyright © 2000 E-ICEBLUE. All Rights Reserved.