Problem in exporting datatable to excel?

Question

Here i am exporting my datatable to excel..But i am getting the error like access to the path is denied I have given full permission to that folder..I dont understand whats the problem..

public void CreateCSVFile(System.Data.DataTable dt, string strFilePath)
{
StreamWriter sw = new StreamWriter(strFilePath, false);
int iColCount = dt.Columns.Count;
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}

Any suggestion?

EDIT:

private void button1_Click(object sender, EventArgs e)
        {
              DirectoryInfo Folder = new DirectoryInfo(textBox3.Text);
                    var strFilePath = Path.Combine(textBox3.Text.Trim(), "Excel");
                    if (Folder.Exists)

                        if (!Directory.Exists(strFilePath))
                            Directory.CreateDirectory(strFilePath);

              string strConn = "user id=**;password=***;initial catalog=***;data source=***;";


            SqlConnection conn = new SqlConnection(strConn);

            conn.Open();

            System.Data.DataTable dt = new System.Data.DataTable();
            SqlDataAdapter da = new SqlDataAdapter("select * from " + textBox1.Text + " where pomas_pono ='" + textBox2.Text+"'", conn);
            da.Fill(dt);
            CreateCSVFile(dt, strFilePath);
conn.Close();
}

What I am looking for is the actually file name with path. I am still seeing the code that makes the filename, but not the final filename. It looks a bit to me like you are trying to write to the folder as if it were a file, but I cannot be certain. What I would like to see is the final result of the strFilePath. Lastly, are you escaping your backslash characters? In other words does your path look like this C:\MyPath\MyFile or like this C:\\MyPath\\MyFile ? c# requires the later. — Harv
– Harv, Commented Jun 14, 2011 at 12:46

will-hart · Accepted Answer · 2012-09-26 10:58:14Z

Try the following code:

private void BindEntryCancellationAudit()
{
        try
        {
            invoice = new BLL_Invoice();
            DataTable dtAuditData = invoice.getAuditDetailsForInvoice();

            progressBar1.Visible = true;
            progressBar1.Minimum = 1;
            progressBar1.Maximum = dtAuditData.Rows.Count;
            progressBar1.Show();
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet1"];
            worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
            worksheet.Name = "Invoice Audit Details";

            for (int i = 1; i < dtAuditData.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dtAuditData.Columns[i-1].ColumnName;
            }

            for (int i = 0; i < dtAuditData.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dtAuditData.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dtAuditData.Rows[i][j].ToString();
                    worksheet.Cells.EntireColumn.AutoFit();
                    if (j + 1 < 17 && j + 1 > 12)
                    {
                        Excel.Range cell = (Excel.Range)worksheet.Cells[i + 2, j + 1];
                        cell.NumberFormat = "0.0000";

                    }
                }
                progressBar1.PerformStep();
            }
            string filepath = ConfigurationManager.AppSettings["ReportLocation"].ToString();
            workbook.SaveAs(filepath+"\\AuditReport.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            app.Quit();
            progressBar1.Hide();
            progressBar1.Visible = false;
            System.Diagnostics.Process.Start(filepath+"\\AuditReport.xls");
            progressBar1.Maximum = 0;
            progressBar1.Minimum = 0;
        }
        catch (Exception ex)
        { throw ex; }
    }

Collectives™ on Stack Overflow

Problem in exporting datatable to excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related