Create invoices with winform app using spire.XLS!?

[Jayanta.KMC]

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 ).

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 )

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

[Elena.Zhang]

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

[Jayanta.KMC]

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

[Elena.Zhang]

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

[Jayanta.KMC]

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 144 times

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

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.

[Elena.Zhang]

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

[Jayanta.KMC]

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 143 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 133 times

Hope you get a little more idea about this now

Regards,
Jayanta

[Elena.Zhang]

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 141 times

Sincerely,
Elena
E-iceblue support team

[Jayanta.KMC]

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 146 times

This is what your code produced:

Result-MergeCells.zip

(11.54 KiB) Downloaded 141 times

And this is the required/expected output:

EXPECTED_RESULT.zip

(12.69 KiB) Downloaded 136 times

Hope this helps further!

[Elena.Zhang]

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

[Jayanta.KMC]

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 136 times

Regards
Jayanta

[Elena.Zhang]

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 133 times

Sincerely,
Elena
E-iceblue support team

[Jayanta.KMC]

Hi,

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

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 120 times

Thanks in advance.

Jayanta

[Elena.Zhang]

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 132 times

Sincerely
Elena
E-iceblue support team

[Jayanta.KMC]

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 139 times