0

i am printing invoices from c# windows forms fetching data from MySql. i want to use template that is designed in excel. i want to replace the values in the template with original values and print multiple bills or multiple copies of bills using that template.

I am successful in replacing the template values with my values but whats happening in my process is

this is what i have tried

xlWorkBook = xlApp.Workbooks.Open(@"C:\\Book1");
xlWorkSheet = xlWorkBook.Worksheets.get_Item(1);
ReplaceValuesInTemplate(xlWorkSheet);
xlWorkBook.SaveAs("C:\\Book2"); //saving as new excel WorkBook-Book2
xlWorkBook = xlApp.Workbooks.Open(@"C:\\Book2"); //Fetching Book2
xlWorkSheet = xlWorkBook.Worksheets[1]; 
xlWorkSheet.PrintOut(From:1,To:1, Copies:2,Preview: false); //printing
xlApp.Quit();

1) I open template from c# and replace the values with original values 2) its creating a new workbook and than i fetch that workbook and print it.

it's fine with single bill , but problem is,

if want to print multiple bills than it will create many workbooks and than i need to fetch them all and print. this creates lots of memory wastage also and seems like not a proper solution.

how can i print multiple bills from c# using template from excel?

Shadow
  • 33,525
  • 10
  • 51
  • 64
vivek agarwal
  • 13
  • 1
  • 6

1 Answers1

0

Your code is sound. If you're having problems with "leaking" Excel instances hanging around after your code is run, there are two potential solutions.

Single-instance solution

Make xlApp a form-level property, which you hold for the lifetime of your form. Then reuse this.xlApp across calls to your code. Then there will only be one Excel instance hanging round for the lifetime of your form.

You might need to resort to flags, locking or pooling to make this thread safe (if your code needs to be thread safe).

Multi-instance solution

You may have the problem of two threads competing to use Book2.xlsx as the same working file and causing conflicts. In this case, instead of Book2.xlsx, generate a unique temporary file name (see here).

To get rid of the stray Excel instances, as explained here, you'll need to run the garbage collector (GC.Collect(); GC.WaitForPendingFinalizers();) when your code returns to the caller.

The multi-instance solution will likely be slower than the single-instance solution, because the memory benefit of not keeping stray instances of Excel around will be cancelled out by the cost of creating & tearing down an Excel.Application instance (at least in terms of CPU and disk I/O).

oshah81
  • 93
  • 4
  • Thank you for replying. There is no leaking problem with the code. I have taken care through garbage collector. I want the simple and genuine solution for printing multiple bills. – vivek agarwal Apr 04 '19 at 01:24
  • I've tested your code and it is working. Are you experiencing a different issue with reusing the same file for each bill? – oshah81 Apr 05 '19 at 15:11