Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 21st, 2008, 10:18 PM
Authorized User
 
Join Date: Aug 2008
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


 
Old August 22nd, 2008, 12:26 AM
Registered User
 
Join Date: Aug 2008
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
 
Old August 22nd, 2008, 12:57 AM
Authorized User
 
Join Date: Aug 2008
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.

 
Old August 22nd, 2008, 03:20 AM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
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 -/
 
Old August 22nd, 2008, 06:44 AM
Authorized User
 
Join Date: Aug 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks buddy...
problem is solved now :)

 
Old August 22nd, 2008, 07:04 AM
Registered User
 
Join Date: Aug 2008
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
 
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();





Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating EXE from VBA - Excel raj_phoenix Excel VBA 8 June 22nd, 2012 05: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 04: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 07:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.