Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 21st, 2008, 11:18 PM
Authorized User
 
Join Date: Aug 2008
Location: Pune, , India.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating Excel File

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


Reply With Quote
  #2 (permalink)  
Old August 22nd, 2008, 01:26 AM
Registered User
 
Join Date: Aug 2008
Location: Gampaha, , Sri Lanka.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old August 22nd, 2008, 01:57 AM
Authorized User
 
Join Date: Aug 2008
Location: Pune, , India.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old August 22nd, 2008, 04:20 AM
samjudson's Avatar
Friend of Wrox
Points: 8,687, Level: 40
Points: 8,687, Level: 40 Points: 8,687, Level: 40 Points: 8,687, Level: 40
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2007
Location: Newcastle, , United Kingdom.
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

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 -/
Reply With Quote
  #5 (permalink)  
Old August 22nd, 2008, 07:44 AM
Authorized User
 
Join Date: Aug 2008
Location: Pune, , India.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks buddy...
problem is solved now :)

Reply With Quote
  #6 (permalink)  
Old August 22nd, 2008, 08:04 AM
Registered User
 
Join Date: Aug 2008
Location: Gampaha, , Sri Lanka.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old March 4th, 2010, 05:02 AM
Registered User
 
Join Date: Mar 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:

Code:
DataTable people = (DataTable)dataGrid.DataSource;

// Create excel file.
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("DataSheet");
ws.InsertDataTable(people, "A1", true);

Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename="
+ "Report.xls");
ef.SaveXls(Response.OutputStream);
Response.End();
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating EXE from VBA - Excel raj_phoenix Excel VBA 8 June 22nd, 2012 06:49 PM
Problem in opening excel file in MS Excel 2000 kallol Visual C++ 0 November 16th, 2007 05:48 AM
Error Creating Excel Object Antony Deepak ASP.NET 1.0 and 1.1 Professional 2 August 2nd, 2006 05:21 AM
Creating an Excel File from .NET with numeric fiel neilwillby VB.NET 2 December 12th, 2005 04:19 PM
Creating an excel file and showing on the form dotnetprogrammer C# 0 May 3rd, 2005 08:36 AM



All times are GMT -4. The time now is 09:29 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.