save datagridview to excel template with subtotal

Last Reply 2 days ago By dharmendr

Posted 14 days ago

HI.

This has worked on excel 2003 but i am having problems in excel2007. The subtotals won't work and the xlsx file is corrupted wen saved.

please assist.

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            string filePath = @"C:\MyExcelsiorData\test2.xltx";
            xlWorkBook = xlApp.Workbooks.Add(filePath);
            string datestr = DateTime.Now.ToShortDateString();
            string fileName = @"C:\MyQuotes\Quote" + " " + txtName.Text.Replace(" ", "") + ".xls";
            //string filename = @"C:\MyQuotes\Quote";
            string quote = txtName.Text.Replace(" ", "") + datestr;
            string salesman = txtEmail.Text.ToUpper();
            string customer = txtName.Text.ToUpper();
            string contact = txtContact.Text.ToUpper();
            //string delivery = txtDel.Text.ToUpper();
            string address2 = txtDel2.Text.ToUpper();
            string address3 = txtDel3.Text.ToUpper();
            string note = txtNote.Text.ToUpper();
            string trans = comboBox1.SelectedItem.ToString();


            int currentSheet = 1;
            int cell = 0;
            int rowsToDisplay = 28;
            int rowsToStart = 20;

            decimal grandTotal = 0;
            //decimal vatPer = 15;
            //double incVat = 1.5;
            for (int row = 0; row < dataGridView1.Rows.Count - 1; row++)
            {
                if (currentSheet <= xlWorkBook.Sheets.Count)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet);
                }
                else
                {
                    var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
                    xlWorkSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[xlWorkBook.Sheets.Count], misValue, misValue, misValue);
                    xlWorkSheet.Name = "Sheet" + currentSheet.ToString();
                }
                xlWorkSheet.get_Range("E11", "E11").Value2 = salesman;
                xlWorkSheet.get_Range("A12", "A12").Value2 = customer;
                xlWorkSheet.get_Range("A13", "A13").Value2 = address2;
                xlWorkSheet.get_Range("A14", "A14").Value2 = address3;
                xlWorkSheet.get_Range("B11", "B11").Value2 = contact;
                //xlWorkSheet.get_Range("D12", "D12").Value2 = delivery;
                xlWorkSheet.get_Range("D12", "D12").Font.Bold = true;
                //xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
                xlWorkSheet.get_Range("B17", "B17").Value2 = DateTime.Now;
                xlWorkSheet.get_Range("E17", "G17").Value2 = quote;
                xlWorkSheet.get_Range("E13", "H13").Value2 = note;
                xlWorkSheet.get_Range("C17", "C17").Value2 = trans;
                //xlWorkSheet.get_Range("F49", "F49").Value2 = "Total(Excl)";
                //xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";

                //xlWorkSheet.get_Range("F50", "F50").Value2 = "Vat @15%";
                //xlWorkSheet.get_Range("F51", "F51").Value2 = "Total(Incl)";



                for (int column = 0; column < dataGridView1.Columns.Count; column++)
                {
                    if (column == 2 || column == 7)
                    {
                        xlWorkSheet.Cells[cell + rowsToStart, column + 1] = "'" + dataGridView1.Rows[row].Cells[column].Value.ToString();
                    }
                    else
                    {
                        xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString();
                    }
                }


                grandTotal += Convert.ToDecimal(dataGridView1.Rows[row].Cells[6].Value);
                if ((row + 1) % rowsToDisplay == 0)
                {
                    xlWorkSheet.get_Range("F49", "F49").Value2 = "total";
                    xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";
                    currentSheet++;
                    cell = 0;
                }
                else if (row + 1 == dataGridView1.Rows.Count)
                {
                    xlWorkSheet.get_Range("F49", "F49").Value2 = "total";
                    xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";

                    //xlWorkSheet.get_Range("E51", "E51").Value2 = "Transport";
                    //xlWorkSheet.get_Range("F51", "F51").Value2 = 0;

                    //xlWorkSheet.get_Range("F52", "F52").Value2 = "Excl Vat";
                    //xlWorkSheet.get_Range("G52", "G52").Value2 = grandTotal;

                    //decimal vatAmount = grandTotal * (vatPer / 100);

                    xlWorkSheet.get_Range("F50", "F50").Value2 = "Vat @15%";
                    xlWorkSheet.get_Range("F50", "F50").Value2 = "=SUM(F20:F48)";

                    xlWorkSheet.get_Range("F51", "F51").Value2 = "Total";
                    xlWorkSheet.get_Range("G51", "G51").Value2 = "=SUM(F49:F50)";
                }
                else
                {
                    cell++;
                }
            }
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);

            }


            xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            //xlWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, filename);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            //releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            MessageBox.Show("Excel 

 

You are viewing reply posted by: dharmendr 13 days ago.
Posted 13 days ago

.xlsx is not a simple spreadsheet.

Starting with Office 2007, the file formats are actually zipped files that contain files within them.

So you need to set the extension to .xlsx and XlFileFormat to xlOpenXMLWorkbook instead of xlWorkbookNormal in the SaveAs method.

gmsahmod says:
 xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

With

xlWorkBook.SaveAs(destinationPath, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

 

gmsahmod says:
xlWorkSheet.Cells[cell + rowsToStart, column + 1] = "'" + dataGridView1.Rows[row].Cells[column].Value.ToString();

You are returning decimal value as text. So the SUM function will not work.

Better keep as number that will affect SUM.

xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString();

Else you have to loop through the record and calculate the sum and set that value in the cell.