Visual Studio .NET - Open Excel File - Asked By Lokesh M on 04-Aug-08 02:37 AM

Hi All,

I would like to

a. open an new excel file from asp.net web application

b. write few records to it

c. Save the excel file with new name

If this is possible please help me

Thanks All



Issue... - Atul Shinde replied to Lokesh M on 04-Aug-08 02:40 AM

You wont be able to open excel file in web application. U have to create instance of Excel Application after that u will get particular cell of excel worksheet then & then only u will be able to write in excel file.

try this... - Vasanthakumar D replied to Lokesh M on 04-Aug-08 02:40 AM

Hi,

try the below one..

Microsoft.Office.Interop.Excel.Application app = new
Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook book = null; //to access to book
Microsoft.Office.Interop.Excel.Worksheet sheet = null; //to access to sheet
Microsoft.Office.Interop.Excel.Range range = null; //to access to a range of
data "A1:B2"


book = app.Workbooks.Open("c:\\test.xls", Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

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

range = sheet.get_Range("A1","B5");

Only take care about release resources from excel. Use ReleaseComObject
foreach com object that you use.

System.Runtime.InteropServices.Marshal.ReleaseComO bject(range);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(book);
System.Runtime.InteropServices.Marshal.ReleaseComO bject(app);

Check this - san san replied to Lokesh M on 04-Aug-08 02:44 AM

Hi
Check these links
http://www.c-sharpcorner.com/UploadFile/ggaganesh/CreateExcelSheet12012005015333AM/CreateExcelSheet.aspx

http://www.codeproject.com/KB/cs/Simple_Excel_Automation.aspx

also look at this forum
http://www.csharpfriends.com/Forums/ShowPost.aspx?PostID=24718

Hope this helps
SAN

See This Code - Sagar P replied to Lokesh M on 04-Aug-08 02:53 AM

private void createDataInExcel(DataSet ds)

{

          Application oXL;

          _Workbook oWB;

          _Worksheet oSheet;

          Range oRng;

          string strCurrentDir = Server.MapPath(".") + "\\reports\\";

          try

          {

                   oXL = new Application();

                   oXL.Visible = false;

                   //Get a new workbook.

                   oWB = (_Workbook)(oXL.Workbooks.Add( Missing.Value ));

                   oSheet = (_Worksheet)oWB.ActiveSheet;

                   //System.Data.DataTable dtGridData=ds.Tables[0];

                   int iRow =2;

                   if(ds.Tables[0].Rows.Count>0)

                   {

                             //     for(int j=0;j<ds.Tables[0].Columns.Count;j++)

                             //     {

                             //      oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

                             //

                             for(int j=0;j<ds.Tables[0].Columns.Count;j++)

                             {

                                       oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

                             }

                             // For each row, print the values of each column.

                             for(int rowNo=0;rowNo<ds.Tables[0].Rows.Count;rowNo++)

                             {

                                       for(int colNo=0;colNo<ds.Tables[0].Columns.Count;colNo++)

                                       {

                                                 oSheet.Cells[iRow,colNo+1]=ds.Tables[0].Rows[rowNo][colNo].ToString();

                                       }

                             }

                             iRow++;

                    }

                    oRng = oSheet.get_Range("A1", "IV1");

                    oRng.EntireColumn.AutoFit();

                    oXL.Visible = false;

                    oXL.UserControl = false;

                    string strFile ="report"+ DateTime.Now.Ticks.ToString() +".xls";//+

                    oWB.SaveAs( strCurrentDir + 
               strFile,XlFileFormat.xlWorkbookNormal,null,null,false,false,XlSaveAsAccessMode.xlShared,false,false,null,null);

                   // Need all following code to clean up and remove all references!!!

                   oWB.Close(null,null,null);

                   oXL.Workbooks.Close();

                   oXL.Quit();

                   Marshal.ReleaseComObject (oRng);

                   Marshal.ReleaseComObject (oXL);

                   Marshal.ReleaseComObject (oSheet);

                   Marshal.ReleaseComObject (oWB);

                   string  strMachineName = Request.ServerVariables["SERVER_NAME"];

                   Response.Redirect("http://" + strMachineName +"/"+"ViewNorthWindSample/reports/"+strFile);

          }

          catch( Exception theException )

          {

                    Response.Write(theException.Message);

          }

}

 

Also go thr these links;

http://www.c-sharpcorner.com/UploadFile/Globalking/datasettoexcel02272006232336PM/datasettoexcel.aspx?ArticleID=0930c1ba-b92e-4063-b278-4e5bd6dddfa6

 

http://www.developerfusion.co.uk/show/4682/2/

 

Best Luck!!!!!!!!!!!!!
Sujit.

See this code - Sagar P replied to Lokesh M on 04-Aug-08 02:53 AM

The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. This article reveals using Excel from a managed application. Excel is the spreadsheet component of Microsoft Office 2000. The majority of Excel programmatic functionality is exposed through Automation via the type library Excel9.olb. The intention of this article is to express that a managed application can interrelate with Excel as a COM server.

The first step is to create a reference in our project to Excel 9.0 Objects Library. By using Tlbimp tool we can generate Excel.dll.

TlbImp Excel9.olb Excel.dll

By adding Excel.dll to our program we can use the functionality of the Excel.

Now let us see in detail how to create an Excel Spreadsheet? & Set values to the cell using C#. The codes for Creating, make visible, add a new workbook and to set a value for cell in the Excel file is shown below.

  1. Creating new excel.application:

    Application exc = new Application();
    if (exc == null
    )
    {
    Console.WriteLine("ERROR: EXCEL couldn't be started");
    return
    0;
    }

  2. To make application visible:

    exc.set_Visible(0, true);

  3. To get the workbooks collection:

    Workbooks workbooks = exc.Workbooks;
    _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0);

  4. To get the worksheets collection:

    _Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
    if (worksheet == null)
    {
    Console.WriteLine ("ERROR in worksheet == null");
    }

  5. To set the value for cell:

    Range range1 = worksheet.get_Range("C1", Missing.Value);
    if (range1 == null)
    {
    Console.WriteLine ("ERROR: range == null");
    }
    const int
    nCells = 1;
    Object[] args1 =
    new
    Object[1];
    args1[0] = nCells;
    range1.GetType().InvokeMember("Value", BindingFlags.SetProperty,
    null, range1, args1);

Example:

using System;
using System.Reflection;
// For Missing.Value and BindingFlags
using System.Runtime.InteropServices;
// For COMException
using
Excel;
class
AutoExcel
{
public static int
Main()
{
Application exc =
new
Application();
if (exc == null
)
{
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return
0;
}
exc.set_Visible(0,
true
);
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null
)
{
Console.WriteLine ("ERROR: worksheet == null");
}
Range range1 = worksheet.get_Range("C1", Missing.Value);
if (range1 == null
)
{
Console.WriteLine ("ERROR: range == null");
}
const int
nCells = 1;
Object[] args1 =
new
Object[1];
args1[0] = nCells;
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty,
null
,range1, args1);
return
100;
}
}

Now let us observe how to send a single dimension array to Excel:

It is similar to set the value for the cell. Only change is we use array as args2[0] = array2.

const int nCell = 5;
Range range2 = worksheet.get_Range("A1", "E1");
int[] array2 = new int
[nCell];
for (int
i=0; i < array2.GetLength(0); i++)
{
array2[i] = i+1;
}
Object[] args2 =
new
Object[1];
args2[0] = array2;
range2.GetType().InvokeMember("Value", BindingFlags.SetProperty,
null
, range2, args2);

http://www.c-sharpcorner.com/UploadFile/ggaganesh/CreateExcelSheet12012005015333AM/CreateExcelSheet.aspx

Best Luck!!!!!!!!!!!!!!!!!!!
Sujit.

excel - sundar k replied to Lokesh M on 04-Aug-08 03:43 AM

Export to Excel

You just have to add COM reference of Excel Object Library to your project and you can use Excel objects to create/load workbook.

In the below link, you will find sample code which will basically take the excel file which is already there in a location and load it with data, save it, you can make a minor change in it and make it work for creating anew workbook. The below statement will create a new workbook fo r you,

Below is the current code in the below link which will opena  existing workbook,
'Start a new workbook
oBooks = oExcel.Workbooks
    oBooks.Open(Server.MapPath(Request.ApplicationPath) & _
    "\MyTemplate.xls")

You can change it as below to create a new workbook,

'create a new workbook
oBooks = oExcel.Workbooks.Add

Workbooks.Add method creates a new workbook and you can refer it through oBooks object.

Use ASP.NET to send functionally rich reports to your users.

http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp

reply - Binny ch replied to Lokesh M on 04-Aug-08 03:43 AM
dlls u have to include
Interop.Excel.dll
Interop.Office.dll
Interop.VBIDE.dl
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.IO;
here is the code to read excel


string path = "E:\\Users\\PDF\\";
string fileToQuery = "PartyPlannerReport.csv";
string sql = "SELECT * FROM [" + fileToQuery + "]";
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";" + "Extended Properties='text;FMT=Delimited(;);HDR=YES'");
myConnection.Open();
OleDbCommand mycommand = new OleDbCommand(sql, myConnection);
OleDbDataReader dr = mycommand.ExecuteReader(CommandBehavior.CloseConnection);
string pttype = null;
string ptpkg = null;
string sttime = null;
string endtime = null;
string ptTime = null;
string ptno = null;
string ptday = null;
string ptdate = null;
string ptDate = null;
string custfname = null;
string custlname = null;
string custName = null;
string gtname1 = null;
string gtname2 = null;
string gt1age = null;
string gt2age = null;
string gt1gender = null;
string gt2gender = null;
string gt1bc = null;
string gt2bc = null;
//string wphone = null;
string hphone = null;
string cphone = null;
// string arena = null;
while (dr.Read())
{
ptno = dr["Res #"].ToString();
pttype = dr["Party Type"].ToString();
ptpkg = dr["Party Package Name"].ToString();
sttime = dr["Start Time"].ToString();
endtime = dr["End Time"].ToString();
ptTime = sttime.Substring(0, 5) + "-" + endtime.Substring(0, 5);
ptday = dr["Party Day"].ToString();
ptdate = dr["Party Date"].ToString();
ptDate = ptday + "," + ptdate;
custfname = dr["Customer First Name"].ToString();
custlname = dr["Customer Last Name"].ToString();
custName = dr["Customer Full Name"].ToString();
gtname1 = dr["GuestName1"].ToString();
gtname2 = dr["GuestName2"].ToString();
gt1age = dr["GuestAge1"].ToString();
gt2age = dr["GuestAge2"].ToString();
gt1gender = dr["GuestGender1"].ToString();
gt2gender = dr["GuestGender2"].ToString();
gt1bc = dr["GuestBC1"].ToString();
gt2bc = dr["GuestBC2"].ToString();
cphone = dr["Cell Phone"].ToString();
hphone = dr["Home Phone"].ToString();
//arena = dr["Arena A"].ToString();
}
myConnection.Close();


hey - Binny ch replied to Lokesh M on 04-Aug-08 03:43 AM
Go through this:
using System;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel;

class Exc
{
public static void Main()
{

Excel.Application oAppln;
Excel.Workbook oWorkBook;
Excel.Worksheet oWorkSheet;
Excel.Range oRange;
try
{
oAppln = new Excel.Application();
oWorkBook = (Excel.Workbook)(oAppln.Workbooks.Add(true));
oWorkSheet = (Excel.Worksheet)oWorkBook.ActiveSheet;

int iRow = 2;

for (int j = 0; j < 8; j++)
{
oWorkSheet.Cells[1, j + 1] = "Column";
}
for (int rowNo = 0; rowNo < 8; rowNo++)
{
for (int colNo = 0; colNo <8; colNo++)
{
oWorkSheet.Cells[iRow, colNo + 1] = "Row";
}
iRow++;

}
oRange = oWorkSheet.get_Range("A1", "IV1");
oRange.EntireColumn.AutoFit();
oAppln.UserControl = false;

string strFile ="c:/"+ "Test" + ".xls";
oAppln.Visible = true;
oWorkBook.SaveAs(strFile, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null,null);


//***** Read Excel Sheet*****

string Path = @"c:\report.xls";
Excel.ApplicationClass app = new ApplicationClass();
Excel.Workbook workBook = app.Workbooks.Open(Path,0,true,5,"","",true,
Excel.XlPlatform.xlWindows,"\t",false,false,0,true,1,0);

Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
object rowIndex = 1;
object colIndex1 = 1;
object colIndex2 = 2;
object collIndex3= 3;
object CollIndex4= 4;

try
{
while(((Excel.Range)workSheet.Cells[rowIndex,colIndex1]).Value2.ToString() !=null )
{
rowIndex = 2+index;
string Name1 = ((Excel.Range)workSheet.Cells[rowIndex,colIndex1]).Value2.ToString();
string Name2 = ((Excel.Range)workSheet.Cells[rowIndex,colIndex2]).Value2.ToString();
string Name3 = ((Excel.Range)workSheet.Cells[rowIndex,colIndex1]).Value2.ToString();
string Name4 = ((Excel.Range)workSheet.Cells[rowIndex,colIndex2]).Value2.ToString();

Console.WriteLine("Name : {0},{1},{2},{3} ",Name1,Name2,Name3,Name4);
index++ ;
}
}
catch(Exception ex)
{
app.Quit();
Console.WriteLine(ex.Message);
}
Console.Read();

}
catch (Exception theException)
{
Console.WriteLine(theException.Message.ToString());
}
}
}
Avoid Office Automation - mv ark replied to Lokesh M on 04-Aug-08 03:48 AM
Microsoft does not recommend or support server-side Automation of Office. Check this MS KB article for detailed info: http://support.microsoft.com/default.aspx/kb/257757

If you need to just generate Excel documents on the fly, consider using this Office XML & CSS alternative  - http://eggheadcafe.com/tutorials/aspnet/6e1ae1a8-8285-4b2a-a89b-fafc7668a782/aspnet-download-as-wor.aspx
Open Exel in ListView - Kalit Sikka replied to Lokesh M on 04-Aug-08 07:13 AM
Create a reference in your project to Excel 9.0 Objects Library.  This is done by right mouse clicking on the References folder in the Solution Explorer and choosing Add Reference. This brings up the Tab Dialog below. Choose the COM Tab and pick Microsoft Excel 9.0 Objects Library.

Figure 2 - Adding an Excel Reference

This action puts an Interop.Excel.dll and Interop.Office.dll into your bin directory so you can manipulate excel.

Now we can declare our Excel Application Object and the compiler will recognize it:

private Excel.Application ExcelObj = null
;

Excel is launched and an Application reference is obtained in the constructor of our form.  First an Excel Application object is constructed.  Then we check to make sure Excel was actually started. If it was, we have a valid application object and we can now use it to open a file:

public
Form1()
{
// Initialize the Windows Components
InitializeComponent();
ExcelObj =
new
Excel.Application();
// See if the Excel Application Object was successfully constructed
if (ExcelObj == null
)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!");
System.Windows.Forms.Application.Exit();
}
// Make the Application Visible
ExcelObj.Visible = true
;
}

The code for opening the Excel file is shown below. The code uses the OpenFileDialog component to get the path name for the Excel file.  The Excel file is opened using the WorkBooks collections' Open method.  This method takes 15 parameters with the following definition.

Function Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMenuRecentlyUsed]) As Workbook

We really are only interested in the FileName, but have added the other default parameters for your reference.  There is also an OpenText method in Workbooks for opening tab or comma delimited text files.

private void menuItem2_Click(object
sender, System.EventArgs e)
{
// prepare open file dialog to only search for excel files (had trouble setting this in design view)
this.openFileDialog1.FileName = "*.xls";if (this
.openFileDialog1.ShowDialog() == DialogResult.OK)
{
// Here is the call to Open a Workbook in Excel
// It uses most of the default values (except for the read-only which we set to true)
Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open( openFileDialog1.FileName, 0, true
, 5,
"", "",
true, Excel.XlPlatform.xlWindows, "\t", false, false,0, true
);
// get the collection of sheets in the workbook
Excel.Sheets sheets = theWorkbook.Worksheets;
// get the first and only worksheet from the collection 
of worksheets
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
// loop through 10 rows of the spreadsheet and place each row in the list view
for (int
i = 1; i <= 10; i++)
{
Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
System.Array myvalues = (System.Array)range.Cells.Value;
string
[] strArray = ConvertToStringArray(myvalues);
listView1.Items.Add(
new
ListViewItem(strArray));
}
}
}

You also may want to note the ConvertToStringArray method which is used to convert a System.Array into a string array.  If someone knows an easier way to do this, please let me know and I'll alter the article.  The problem is that an Excel Array comes back as two dimensional even if you are selecting a range of a single row, so you need to change the 2-d array into something the listview can accept. Also the listview array is 0 based and the Excel range array is 1 based.

string
[] ConvertToStringArray(System.Array values)
{
// create a new string array
string[] theArray = new string
[values.Length];
// loop through the 2-D System.Array and populate the 1-D String Array
for (int
i = 1; i <= values.Length; i++)
{
if (values.GetValue(1, i) == null
)
theArray[i-1] = "";
else
theArray[i-1] = (string
)values.GetValue(1, i).ToString();
}
return
theArray;
}

Go through the folowing code - Sunil Supale replied to Lokesh M on 04-Aug-08 10:56 AM
I have implemented this code  to  convert you dataset to  Excel plz follow tha same proce to do ur task.

public static void ExportToExcel(DataSet dataSet, string outputPath)
        {
            #region Export Your Result To Excel Sheet.
            // Create the Excel Application object
            try
            {
                Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

                // Create a new Excel Workbook
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

                int sheetIndex = 0;

                // Copy each DataTable
                foreach (System.Data.DataTable dt in dataSet.Tables)
                {

                    // Copy the DataTable to an object array
                    object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                    // Copy the column names to the first row of the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        rawData[0, col] = dt.Columns[col].ColumnName;
                    }

                    // Copy the values to the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        for (int row = 0; row < dt.Rows.Count; row++)
                        {
                            rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                        }
                    }

                    // Calculate the final column letter
                    string finalColLetter = string.Empty;
                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                    int colCharsetLen = colCharset.Length;

                    if (dt.Columns.Count > colCharsetLen)
                    {
                        finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                    }

                    finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);

                    // Create a new Sheet
                    Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex), Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
                    excelSheet.Name = dt.TableName;

                    // Fast data export to Excel
                    string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);

                    excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
                    // Mark the first row as BOLD
                    ((Excel.Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
                }

                // Save and Close the Workbook
                excelWorkbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelWorkbook.Close(true, Type.Missing, Type.Missing);
                excelWorkbook = null;

                // Release the Application object
                excelApp.Quit();
                excelApp = null;

                // Collect the unreferenced objects
                GC.Collect();
                GC.WaitForPendingFinalizers();
                MessageBox.Show("The Search Result Has Been Stored TO Location " + outputPath, " Resume Parser", MessageBoxButtons.OK, MessageBoxIcon.Information);


            }
            catch (Exception ex)
            {
                Errordetector.Class1 EM = new Errordetector.Class1();
                EM.writeLog("ERROR FORUND AT Search result,Export to databass" + DateTime.Now.ToString());
                MessageBox.Show("Error in Excel Operation: " + ex.ToString(), "Resume Parser", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            #endregion
        }

Filip Krnjic replied to Lokesh M on 31-Jul-09 07:18 AM
Hi,

there are different ways to do it. For sure most popular is to use Interop (I see that some people have already described how to do it with Interop). But Interop has many issues that could cause you trouble:
1.) very slow for large number of data
2.) doesn't work on machines that have same version of Excel that you use
3.) very complicated to use

On contrary, you can try to use some 3rd party component. 3rd party components usually solve all of those 3 problems. For example you can try GemBox http://www.gemboxsoftware.com/GBSpreadsheet.htm which is free if you need for working with smaller files.
John Glenn replied to Lokesh M on 28-Nov-11 03:49 AM
Hi,

you can easily create XLS, XLSX, CSV, ODS or HTML files easily in http://www.gemboxsoftware.com/support/articles/asp-net-excel applications with this http://www.gemboxsoftware.com/spreadsheet/overviewlibrary.

Here is a sample http://www.gemboxsoftware.com/spreadsheet/overview code how to accomplish your task by writing http://www.gemboxsoftware.com/support/articles/import-export-datatable-xls-xlsx-ods-csv-html-net file:

var ef = new ExcelFile();

 

ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);

 

ef.SaveXls(dataTable.TableName + ".xls");