how to create .xls 2003 file at specified path from datatable / dataset,

Question

0.00/5 (No votes)

See more:

Hi All,

Can any1 plz help me to create excel(.xls)file of 2003 version from dataset / datatable without any warning or alert messages while opening a file,
I am using IIS 2008 x64 server there I din't find Ms Office files, But task is that the created .xls file should open / work in server also....

please help,

Thanks in advance,

[Edit - further detail from OP]
once I create xls and while opening this alert msg should not show...

Quote:
"The file you are trying to open, 'someFileName.xls, is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

and the below code which I used with the help of Microsoft.Office.Interop.Excel;
Its working fine in Local Host without any warning / alert msg but in sever its not working Plz help

C#

oleda1.Fill(ds);
 
 Microsoft.Office.Interop.Excel.Application oAppln;
//declaring work book
Microsoft.Office.Interop.Excel.Workbook oWorkBook;
//declaring worksheet
Microsoft.Office.Interop.Excel.Worksheet oWorkSheet;
oAppln = new Microsoft.Office.Interop.Excel.Application();
oWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(oAppln.Workbooks.Add(true));
//oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkBook.ActiveSheet;
int i2 = 0;
foreach (DataTable table in ds.Tables)
{
    //oWorkSheet = new Microsoft.Office.Interop.Excel.Worksheet();
    oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)(oWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing));
    if (i2 == 0)
    {
        oWorkSheet.Name = "first";
    }
    else
    {
        oWorkSheet.Name = "second";
    }
    oWorkSheet.Activate();
    //oWorkBook.Worksheets.Add(null, null, 1, null);
    //DataTable table = DATASETNAME.Tables[0];
    int ColumnIndex = 0;
 
    foreach (DataColumn col in table.Columns)
    {
        ColumnIndex++;
 
        oWorkSheet.Cells[1, ColumnIndex] = col.ColumnName;
    }
    int rowIndex = 0;
    foreach (DataRow row in table.Rows)
    {
        rowIndex++;
        ColumnIndex = 0;
        foreach (DataColumn col in table.Columns)
        {
            ColumnIndex++;
            oWorkSheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString();
        }
    }
    // Worksheet worksheet = (Worksheet)oAppln.ActiveSheet;
    //worksheet.Activate();
    i2++;
}

Posted 8-May-13 1:27am

GaviGunda

Updated 8-May-13 1:57am

CHill60

v2

Add a Solution

Comments

CHill60 8-May-13 7:33am

Your post isn't very clear - what have you tried so far and what are the warning / alert messages that you're getting?

GaviGunda 8-May-13 7:51am

once I create xls and while opening this alert msg should not show...
"The file you are trying to open, 'someFileName.xls, is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

and the below code which I used with the help of Microsoft.Office.Interop.Excel;
Its working fine in Local Host without any warning / alert msg but in sever its not working Plz help

oleda1.Fill(ds);

Microsoft.Office.Interop.Excel.Application oAppln;
//declaring work book
Microsoft.Office.Interop.Excel.Workbook oWorkBook;
//declaring worksheet
Microsoft.Office.Interop.Excel.Worksheet oWorkSheet;
oAppln = new Microsoft.Office.Interop.Excel.Application();
oWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(oAppln.Workbooks.Add(true));
//oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkBook.ActiveSheet;
int i2 = 0;
foreach (DataTable table in ds.Tables)
{
//oWorkSheet = new Microsoft.Office.Interop.Excel.Worksheet();

oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)(oWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing));
if (i2 == 0)
{
oWorkSheet.Name = "first";
}
else
{
oWorkSheet.Name = "second";
}
oWorkSheet.Activate();
//oWorkBook.Worksheets.Add(null, null, 1, null);
//DataTable table = DATASETNAME.Tables[0];
int ColumnIndex = 0;

foreach (DataColumn col in table.Columns)
{
ColumnIndex++;

oWorkSheet.Cells[1, ColumnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
oWorkSheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString();
}
}

// Worksheet worksheet = (Worksheet)oAppln.ActiveSheet;
//worksheet.Activate();
i2++;
}

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KM Perumal · Answer 1 · 2013-05-08T02:31:00

C#

private void button8_Click(object sender, RoutedEventArgs e)
       {
           SaveFileDialog sd = new SaveFileDialog();
           sd.Filter = "Excel File (.xls)|*.xls";
           sd.Title = "Save File";
           sd.AddExtension = true;
           sd.ShowDialog();
           if (sd.FileName != "")
           {
               OleDbConnection data = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +Wr u want save path + ";Extended Properties='Excel 8.0;HDR=Yes'");
               data.Open();
               try
               {
                   OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ( [c1] string, [c2] string)", data);
                   cmd.ExecuteNonQuery();
                   for (int i = 0; i < yourDataset.tables[0].rows.count; i++)
                   {
                       cmd = new OleDbCommand("INSERT INTO [Sheet1] ( [c1], [c2])Values(yourDataset.tables[0].rows[i].ittemarray[0].tostring() + "','" +yourDataset.tables[0].rows[i].ittemarray[1].tostring()+ "')", data);
                       cmd.ExecuteNonQuery();
                   }
               }
               catch
               {
               }
               finally
               {
                   data.Close();
               }
           }
       }