开发者

Problem in exporting datatable to excel?

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)

        开发者_StackOverflow中文版                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();
}


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; }
    }
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜