none
C# and Excel, Open Existing Formatted Spreadsheet, Copy DGV Info, Save And Print RRS feed

  • Question

  • This should be simple, but I can't quite get the set up right.

    I have a point of sales system I'm writing, VS 2010 if that's relevant.  Add items to the "cart" aka the datagridview, all that stuff works fine.  What I need to be able to do, I have an Excel workbook, the first sheet is formatted as a sales receipt.  I need to be able to open it, copy the data, and save a copy as a new file elsewhere, and leave the original intact for the next transaction.  So far I haven't found exactly what I'm looking for on it.  The excel file I have already has the logo, and a few other touches on it so I just want to keep reusing that template.

    Can someone help me on this?  I'm drawing a total blank, all I've found is create a new one every time or just completely overwrite the file every time.  Thanks in advance!  PS sorry if this is the wrong category on the forums, it's gotten cluttered since the last time I was a regular user lol.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    • Moved by Kristin Xie Wednesday, October 15, 2014 7:56 AM
    Tuesday, October 14, 2014 7:01 PM

Answers

  • Hi psifreak,

    Like your title mentioned, this case related to excel, so i will move your case to Microsoft Office for Developers >Excel for Developers forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by psifreak Tuesday, October 21, 2014 5:19 PM
    Wednesday, October 15, 2014 7:58 AM
  • Hi psifreak,

    For your requirement, I suggest that you could use excel template, for this way, users could base on this template to do other actions and save it without change the template.

    You could save your excel file as template or create an excel template add-in.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by psifreak Tuesday, October 21, 2014 5:19 PM
    Thursday, October 16, 2014 9:20 AM
    Moderator
  • Hi psifreak,

    Base on your code, you are save the file as the standard file (xls). What I mean in my previous reply is use template, the extension is .xltx and the path is like C:\Users\[user name]\Documents\Custom Office Templates.

    After that, you could create a new excel file base on that template. (You need use the compatible version file, the higher version of office can compatible lower version of office)

    There are some links that can help you:

    # Templates

    http://www.excel-easy.com/basics/templates.html

    # XlFileFormat Enumeration (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff198017(v=office.15).aspx

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by psifreak Tuesday, October 21, 2014 5:19 PM
    Tuesday, October 21, 2014 9:28 AM
    Moderator

All replies

  • Hi psifreak,

    Like your title mentioned, this case related to excel, so i will move your case to Microsoft Office for Developers >Excel for Developers forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by psifreak Tuesday, October 21, 2014 5:19 PM
    Wednesday, October 15, 2014 7:58 AM
  • Ok, I need the C# code for Visual Studio for this, so I guess I figured wrong.  Thanks for that!

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Wednesday, October 15, 2014 4:25 PM
  • Hi psifreak,

    For your requirement, I suggest that you could use excel template, for this way, users could base on this template to do other actions and save it without change the template.

    You could save your excel file as template or create an excel template add-in.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by psifreak Tuesday, October 21, 2014 5:19 PM
    Thursday, October 16, 2014 9:20 AM
    Moderator
  • I've been working with this but my issue just hit a new level.  On my computer it works fine, opens the template, writes, saves it as a new file and gives me a print dialog.  But on my computer it's running excel 2013, and on a different computer that needs this program, it's excel 2010.  It stopped throwing excel errors when I hit the print button to trigger the below code, but the other computer doesn't do anything with the excel at all, doesn't open, save, or do a print dialog.  Nothing.  I'm not sure.  I've tried .xls, xlsx, and the xlam formats for the saved template, but it still doesn't respond. I added both Excel references from VS (it's 2010 pro), the 12 and 14 ones, still nothing.  So far none of the "compatibility" formats are working on the second computer.

                    Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                        ExcelApp.Application.Workbooks.Add(@"template.xls"  );
                    Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[1];

                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.Columns.Count - 4; j++)
                        {
                            Sheet1.Cells[i + 16, j + 1] = Convert.ToString(dataGridView1.Rows[i].Cells[j].Value);
                        }
                    }
                    FilePath2 = @"save location ".xls";

                    if (FilePath2 != string.Empty)
                    {
                        ExcelApp.ActiveWorkbook.SaveAs(FilePath2, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

                        ExcelApp.ActiveWorkbook.Saved = true;
                        ExcelApp.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlDialogPrint].Show();
                        ExcelApp.Quit();


                    }


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Monday, October 20, 2014 3:43 PM
  • Hi psifreak,

    Base on your code, you are save the file as the standard file (xls). What I mean in my previous reply is use template, the extension is .xltx and the path is like C:\Users\[user name]\Documents\Custom Office Templates.

    After that, you could create a new excel file base on that template. (You need use the compatible version file, the higher version of office can compatible lower version of office)

    There are some links that can help you:

    # Templates

    http://www.excel-easy.com/basics/templates.html

    # XlFileFormat Enumeration (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff198017(v=office.15).aspx

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by psifreak Tuesday, October 21, 2014 5:19 PM
    Tuesday, October 21, 2014 9:28 AM
    Moderator
  • I mentioned in the first post, I've tried the xlts.  What's happening is that her computer is blatantly not interacting with excel, period.  It doesn't open the file(template.xls or any extension), it doesn't save the new copy of the file, and it doesn't show the print dialog.  If i double click on her computer, excel opens the template.  The program does not.

    On my computer the code opens the template, writes the data to it, saves it as a new copy elsewhere, and offers a printdialog.  Hers does none of the above and that is the essence of the problem that I'm having right now.  I upgraded her excel to the same version as mine, and still no results.  Mine saves exactly as it should, hers for some reason doesn't save with a file name the program designates, and doesn't even give it an extension, xls, xlsx, or otherwise.  Just a blank undefined file with a name I didn't specify.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Tuesday, October 21, 2014 3:00 PM