HEllo guys,
i am using listview control in C#.
I need to create Excel file runtime and want to save all Listview content into it.
I have done some coding but i could found only
"theWorkbook = ExcelObj.Workbooks._Open(...)" method.
I couldn't find any method to create file.I am also facing some problem in saving and closing file as the file is getting opened in 'Read Only' type.
Excel object remains undestroyed even after application close.


Code that i have written is as follows.

private void saveLog_btn_Click(object sender, EventArgs e)
    {
      Microsoft.Office.Interop.Excel.Workbook theWorkbook;
      Microsoft.Office.Interop.Excel.Worksheet worksheet;
      Microsoft.Office.Interop.Excel.Application ExcelObj = null;

      string strMessage = "File created at " + m_PathOfLog;
      try
      {
        ExcelObj= new Microsoft.Office.Interop.Excel.Application();
        theWorkbook = ExcelObj.Workbooks._Open(m_PathOfLog, 0, false, 5,
        "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindow s, "\t", true, true, 0, true);

 Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.g et_Item(1);

        int i = 1;
        foreach (ListViewItem currItem in lstViewResult.Items)
        {
            worksheet.Cells[i, 1] = i;
            worksheet.Cells[i, 2] = currItem.SubItems[0].Text;
            if (currItem.SubItems.Count <= 1)
            {
              worksheet.Cells[i, 3] = "";
            }
            else
            {
              worksheet.Cells[i, 3] = currItem.SubItems[1].Text;
            }
            i = i + 1;
        }
        ExcelObj.Save(m_PathOfLog);
        ExcelObj.Workbooks.Close();
        theWorkbook.Close((System.Boolean)false, m_PathOfLog, System.Type.Missing);
        ExcelObj.Quit();
}

I request you to help me to find solution to this problem.

Regards,
Vikas

Hi Friend,
Here i am puting you code which will help u to do the task.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using RetrieveSend;
using Excel;
using CommonClassLib;
using System.Data.SqlClient;
using GITExceptionLib;
using CompressFolders;
using ICSharpCode.SharpZipLib.Checksums;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.GZip;
using System.IO;
using System.Net.Mail;



namespace DataTransferNN
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

//generate Excel sheets 1

        public void transferDetails()
        {

        Excel.Application excel= new Excel.Application();
            int rowIndex=1;
            int colIndex=0;

            excel.Application.Workbooks.Add(true);
            Retrieve rtn=new Retrieve();

//From list put to the names list

            List<string> names = addList();
            if (names != null)
            {
                foreach (string name in names)
                {
                    System.Data.DataTable table = rtn.retrieveSqlServer(name);
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        excel.Cells[1, colIndex] = col.ColumnName;
                    }

                    foreach (DataRow row in table.Rows)
                    {
                        rowIndex++;
                        colIndex = 0;
                        foreach (DataColumn col in table.Columns)
                        {
                            colIndex++;
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                    excel.Visible = false;
                    try
                    {
                        if (File.Exists("C:\\TFiles\\" + name + ".xls"))
                        {
                            File.Delete("C:\\ Files\\" + name + ".xls");
                            MessageBox.Show("File deleted" + name);
                        }
                        excel.ActiveWorkbook.SaveAs("C:\\GITFiles\\" + name + ".xls",
                        Excel.XlFileFormat.xlWorkbookNormal,
                        null,
                        null,
                        false,
                        false,
                        Excel.XlSaveAsAccessMode.xlShared,
                        false,
                        false,
                        null,
                        null,
                        null);

                    }
                    catch
                    {
                        SQLHandler.FileCantSave();

                    }

                }
            }
        }

Here I have use a data table
Best rgds.,
Dayan

dayancode

Microsoft.Office.Interop.Excel.Workbook book;
Microsoft.Office.Interop.Excel.Worksheet sheet;
Microsoft.Office.Interop.Excel.Application application;

application = new Microsoft.Office.Interop.Excel.ApplicationClass();
book = application.Workbooks.Add(Type.Missing);

sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Wor ksheets[1];

sheet.Name = "sheet one";

sheet.Cells[1, 1] = "Emp";
sheet.Cells[1, 2] = "ID";

sheet.Columns.AutoFit();

book.SaveAs("c:\\temp.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLS preadsheet, Type.Missing, Type.Missing,
                false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode. xlNoChange,
                Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

book.Close(true, Type.Missing, Type.Missing);
application.Quit();



size of excel file generated by above code : 3 kb
size of excel file if created manually : 12 kb

So it will throw an exception while opening and reading the file.

Excel is not supported in a server environment (due to the fact it doesn't close down properly as you noted) as it is a Client application and not designed to be run on the server.

Most people use the fact that Excel will quite happily open an HTML file in Excel if you simply rename it to .XLS, specifically if you export a gridview of listview as a table.

There are numerous solutions available on the internet for this very common problem, so can I suggest you try searching google.

/- Sam Judson : Wrox Technical Editor -/

Thanks buddy...
problem is solved now :)

I am very Happy to here that u have solved the problem now. Hope my eg. helped u to do it.
Best Rgds.,
Dayan
:)

dayancode

Hi,

if you are looking for more advanced exporting to Excel (for example, to include styles, data types information, formulas, document properties etc..) then simple transformation of ListView to HTML and letting the Excel app to open that HTML won't be enough.

For this kind of Excel ASP.NET exporting scenarios, the best solution is some 3rd party product, because Excel Automation in server environment is not good solution because of security and performance reasons.

Here is a code snippet how to export DataGrid to Excel with this Excel C# component: