|
Subject:
|
Creating Excel File
|
|
Posted By:
|
vikas67k
|
Post Date:
|
8/21/2008 10:18:21 PM
|
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.xlWindows, "\t", true, true, 0, true);
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_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 By:
|
dayancode
|
Reply Date:
|
8/22/2008 12:26:37 AM
|
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 By:
|
vikas67k
|
Reply Date:
|
8/22/2008 12:57:46 AM
|
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.Worksheets[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.xlXMLSpreadsheet, 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 By:
|
samjudson
|
Reply Date:
|
8/22/2008 3:20:33 AM
|
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 By:
|
vikas67k
|
Reply Date:
|
8/22/2008 6:44:51 AM
|
Thanks buddy...
problem is solved now :)
|
|
Reply By:
|
dayancode
|
Reply Date:
|
8/22/2008 7:04:20 AM
|
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
|
