无法生成.xlsx文件 [英] Unable to generate the .xlsx file

查看：92 发布时间：2019/6/17 18:47:36 C#3.0 C# ASP.NET MS-Excel

本文介绍了无法生成.xlsx文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个文本文件，在tab deliminimin中，以下是我生成Excel的代码。

  protected   void  to_excel（ object  sender，EventArgs e）
 {
  string  filepath = Path.Combine（Server.MapPath（ 〜/ Files），fileupload.FileName）; 
 fileupload.SaveAs（filepath）; 
  string  fname = fileupload.PostedFile.FileName; 
 DataTable dt =（DataTable）ReadToEnd（filepath）; 
  string  sFilename = fname.Substring（ 0 ，fname.IndexOf（ 。））; 
 HttpResponse response = HttpContext.Current.Response; 
 Response.Clear（）; 
 Response.AddHeader（  content-disposition，  attachment; filename = + sFilename +   XLS）; 
 Response.Charset =  ; 
 Response.Cache.SetCacheability（HttpCacheability.NoCache）; 
 Response.ContentType =   application / vnd.openxmlformats-officedocument.spreadsheetml.sheet; 
 System.IO.StringWriter stringWrite =  new  System.IO.StringWriter（）; 
 System.Web.UI.HtmlTextWriter htmlWrite =  new  System.Web.UI.HtmlTextWriter（stringWrite）; 
 System.Web.UI.WebControls.DataGrid dg =  new  System.Web.UI.WebControls.DataGrid（）; 
 dg.DataSource = dt; 
 dg.DataBind（）; 
 dg.RenderControl（htmlWrite）; 
 Response.Write（stringWrite.ToString（））; 
 Response.End（）; 
} 
 私有 对象 ReadToEnd（ string  filePath）
 {
 DataTable dtDataSource =  new  DataTable（）; 
  string  [] fileContent = File.ReadAllLines（filePath）; 
  if （fileContent.Count（）>   0 ）
 {
  string  [] columns = fileContent [ 0 ] .Split（'  \t'）; 
  for （ int  i =  0 ; i <  columns.Count（）; i ++）
 {
 dtDataSource.Columns.Add（columns [i]）; 
} 
  for （ int  i =  1 ; i <  fileContent.Count（）; i ++）
 {
  string  [] rowData = fileContent [i] .Split（'  \t'）; 
 dtDataSource.Rows.Add（rowData）; 
} 
} 
  return  dtDataSource; 
}

此代码工作正常，因为我生成2003 excel文件（.xls）。

但如果我通过将代码更改为生成2007（.xlsx）

 Response.AddHeader（  content-disposition，  attachment; filename = + sFilename +   .xlsx）;

i得到如下错误http://s11.postimage.org/i2xt9tonn/error .jpg

我完成了我的作业，并且发现这个错误是因为我的程序生成的.xlsx文件是使用HTML（标记语言）XML完成的（标记语言）实际上应该为2007 excel文件完成。

我的问题是我应该做些什么改变才能得到理想的结果，即我得到2007年excel sheet !!!

解决方案

你必须使用扩展库，我推荐使用EPPlus，这是一个读取&的.net库。使用Open Office Xml格式（xlsx）编写Excel 2007/2010文件。 http://epplus.codeplex.com

然后更换代码

 受保护  void  to_excel（ object  sender，EventArgs e）
 {
  string  filepath = Path.Combine（Server.MapPath（  〜/ Files），fileupload.FileName）; 
 fileupload.SaveAs（filepath）; 
  string  fname = fileupload.PostedFile.FileName; 
 DataTable dt =（DataTable）ReadToEnd（filepath）; 
  string  sFilename = fname.Substring（ 0 ，fname.IndexOf（ 。））; 
 sFilename = sFilename +  。xlsx; 
 MemoryStream ms = DataTableToExcelXlsx（dt，  Sheet1）; 
 ms.WriteTo（HttpContext.Current.Response.OutputStream）; 
 HttpContext.Current.Response.ContentType =   application / vnd.openxmlformats-officedocument.spreadsheetml.sheet< /跨度>; 
 HttpContext.Current.Response.AddHeader（  Content-Disposition，  attachment; filename = + sFilename）; 
 HttpContext.Current.Response.StatusCode =  200 ; 
 HttpContext.Current.Response.End（）; 
}

  public   void  toexcel（DataTable dt， string 文件名）
 {
 MemoryStream ms = DataTableToExcelXlsx（dt，  Sheet1）; 
 ms.WriteTo（HttpContext.Current.Response.OutputStream）; 
 HttpContext.Current.Response.ContentType =   application / vnd.openxmlformats-officedocument.spreadsheetml.sheet< /跨度>; 
 HttpContext.Current.Response.AddHeader（  Content-Disposition，  attachment; filename = + Filename）; 
 HttpContext.Current.Response.StatusCode =  200 ; 
 HttpContext.Current.Response.End（）; 
} 
  public   bool  IsReusable 
 {
 < span class =code-keyword> get  { return   false ; } 
} 
  public  静态 MemoryStream DataTableToExcelXlsx（DataTable表， string  sheetName）
 {
 MemoryStream Result =  new  MemoryStream（）; 
 ExcelPackage pack =  new  ExcelPackage（）; 
 ExcelWorksheet ws = pack.Workbook.Worksheets.Add（sheetName）; 
  int  col =  1 ; 
  int  row =  1 ; 
  foreach （DataColumn column  in  table.Columns）
 {
 ws.Cells [row，col] .Value = column.ColumnName.ToString（）; 
 col ++; 
} 
 col =  1 ; 
 row =  2 ; 
  foreach （DataRow rw  in  table.Rows）
 {
  foreach （DataColumn cl  in  table.Columns）
 {
  if （rw [cl.ColumnName]！= DBNull.Value）
 ws.Cells [row，col] .Value = rw [cl.ColumnName] .ToString（）; 
 col ++; 
} 
 row ++; 
 col =  1 ; 
} 
 pack.SaveAs（Result）; 
  return 结果; 
}

我在这里得到了这个解决方案http://forums.asp.net/p/1863741/5230401.aspx / 1？无法+ +生成+ + xlsx +文件+

在任何按钮控件中尝试此编码...这个编码肯定对你有用....
 //创建Excel应用程序
 
 Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application（）; 
 //创建Excel应用程序
 Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add（Type.Missing）; 
 
 //Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application（）; 
 
 
 Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet（）; 
 //Microsoft.Office.Interop.Excel.Workbook objWorkBook = null; 
 
 
 //查看程序背后的excel表
 
 app.Visible = true; 
 
 
 //获取第一张纸的参考。默认情况下，其名称为Sheet1。 
 
 //存储对工作表的引用
 objSheet =（Microsoft.Office.Interop.Excel.Worksheet）workbook.Sheets [Sheet1]; 
 objSheet =（Microsoft.Office.Interop.Excel.Worksheet）workbook.ActiveSheet; 
 
 
 //更改活动工作表的名称
 
 objSheet.Name =从gridview导出; 
 
 
 //在Excel中存储标题部分
 
 //// for（int j = 1; j< dataGridView1.Columns.Count + 1; j ++ ）
 //// {
 
 //// objSheet.Cells [1，j] = dataGridView1.Columns [j - 1] .HeaderText; 
 
 ////} 
 
 
 //将每行和每列值存储到excel表
 
 for（int k = 0 ; k< dataGridView1.Rows.Count - 1; k ++）
 {
 
 for（int l = 0; l< dataGridView1.Columns.Count; l ++）
 {
 
 objSheet.Cells [k + 1，l + 1] = dataGridView1.Rows [k] .Cells [l] .Value.ToString（）; 
 
} 
 
} 
 
 
 
 //保存申请
 
 //工作簿.SaveAs（@C：\\ Book1.xml，Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Microsoft.Office.Interop。 Excel.XlSaveAsAccessMode.xlNoChange，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing）; 
 
} 
 
或尝试以下链接： b $ b

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas（v = VS.80）的.aspx [< a href =http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(v=vs.80).aspx\"target =_ blanktitle =New使用System;

使用System.Collections.Generic;

;

使用System.Data;

使用System.Drawing;

使用System.Linq;

使用System .Text;

使用System.Windows.Forms;

使用System.Data.SqlClient;

使用Microsoft.Office;

名称空间Littleflower

{

公共部分班级过滤器：表格

{

SqlCommand cmd;

SqlConnection con;

SqlDataAdapter da;

SqlDataReader dr;

DataSet ds;

//公共枚举XlSaveAsAccessMode

//公共枚举XlSaveConflictResolution

公共过滤器（）

{

InitializeComponent（）;

con = new SqlConnection（Data Source = FABSYS27 \\ SQLEXPRESS;初始目录=问题;用户ID = sa; Pwd = saadmin）;

}

private void Form1_Load （对象发送者，EventArgs e）

{

con.Open（）;

cmd =新的SqlCommand （从主题中选择不同（主题名称），con）;

dr = cmd.ExecuteReader（）;

comboBox1.Items.Clear（）;

while（dr.Read（））

{

if（dr [0] .ToString（）！=）

{

comboBox1.Items.Add（dr [0] .ToString（））;

}

}

dr.Close（）;

con.Close（）;

con.Open（）;

string a =;

SqlCommand c = new SqlCommand（从questiontype中删除，其中questions =''+ a +''，con）;

c.Execu teNonQuery（）;

con.Close（）;

con.Open（）;

String strSQL =select q.questions from subject s，questiontype q其中q.subjectid = s.subjectid;

SqlDataAdapter dataAdapter = new SqlDataAdapter（strSQL，con）;

SqlCommandBuilder commandBuilder = new SqlCommandBuilder（dataAdapter）;

//填充新数据表并将其绑定到BindingSource。

DataTable table = new DataTable（）;

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill（table）;

dbBindSource.DataSource = table;

//最后将数据绑定到网格

dataGridView1.DataSource = dbBindSource;

con.Close（）;

}

 

private void comboBox1_SelectedIndexChanged（object sender，EventArgs e） b $ b $

private void comboBox3_SelectedIndexChanged（object sender，EventArgs e ）

{

}

private void dataGridView1_CellContentClick（object sender，DataGridViewCellEventArgs e）

{

}

private void comboBox1_SelectedIndexChanged_1（object sender，EventArgs e）

{

con.Open（）;

String strSQL =select q.questions from subject s，questiontype q where q.subjectid = s.subjectid and s.subjectname =''+ comboBox1 。选择edItem.ToString（）+'';

SqlDataAdapter dataAdapter = new SqlDataAdapter（strSQL，con）;

SqlCommandBuilder commandBuilder = new SqlCommandBuilder（dataAdapter）;

//填充新的数据表并将其绑定到BindingSource。

DataTable table = new DataTable（）;

table .Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill（table）;

dbBindSource.DataSource = table;

//最后将数据绑定到网格

dataGridView1.DataSource = dbBindSource;

con.Close（）;

con .Open（）;

cmd =新的SqlCommand（从主题中选择不同（主题），其中su bjectname =''+ comboBox1.SelectedItem +''，con）;

dr = cmd.ExecuteReader（）;

comboBox3.Items.Clear（）;

while（dr.Read（））

{

if（dr [0] .ToString（）！=）

{

comboBox3.Items.Add（dr [0] .ToString（））;

}

}

dr.Close（）;

con.Close（）;

}

private void comboBox3_SelectedIndexChanged_1（object sender，EventArgs e）

$

con.Open（）;

String strSQL =select q.questions from subject s，questiontype q where q.subjectid = s.subjectid and s。 subjectname =''+ comboBox1.SelectedItem.ToString（）+''和s.topic =''+ comboBox3.SelectedItem.ToString（）+'';

SqlDataAdapter dataAdapter = new SqlDataAdapter（strSQL，con）;

SqlCommandBuilder commandBuilder = new SqlCommandBuilder（dataAdapter）;

//填充新数据表并将其绑定到BindingSource。

DataTable table = new DataTable（）;

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill（table）;

dbBindSource.DataSource = table;

//最后将数据绑定到网格

dataGridView1.DataSource = dbBindSource;

con.Close（）;

con.Open（）;

cmd = new SqlCommand（select distinct（mark））来自subject，其中subjectname =''+ comboBox1.SelectedItem +''和topic =''+ comboBox3.SelectedItem +''，con）;

dr = cmd.ExecuteReader（）;

comboBox2.Items.Clear（）;

while（dr.Read（））

{

if（dr [0] .ToString（）！=）

{

comboBox2.Items.Add（dr [0] .ToString（））;

}

}

dr.Close（）;

con.Close（）;

}

private void comboBox2_SelectedIndexChanged（object sender，EventArgs e）

{

con.Open（） ;

String strSQL =select q.questions from subject s，questiontype q where q.subjectid = s.subjectid and s.subjectname =''+ comboBox1.SelectedItem.ToString（）+' '和s.topic =''+ comboBox3.SelectedItem.ToString（）+''和s.mark =+ comboBox2.SelectedItem.To String（）+;

SqlDataAdapter dataAdapter = new SqlDataAdapter（strSQL，con）;

SqlCommandBuilder commandBuilder = new SqlCommandBuilder（dataAdapter）;

//填充新数据表并绑定它到BindingSource。

DataTable table = new DataTable（）;

table.Locale = System .Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill（table）;

dbBindSource.DataSource = table;

//最后将数据绑定到网格

dataGridView1.DataSource = dbBindSource; 

con.Close（）;

}

private void comboBox4_SelectedIndexChanged（object sender，EventArgs e）

{

con.Open（）;

试试

{

String strSQL =SELECT TOP+ comboBox4.SelectedItem +q.questions FROM questiontype q，subject s where q.subjectid = s.subjectid and s.subjectname =''+ comboBox1.SelectedItem.ToString（）+''and s.topic =''+ comboBox3.SelectedItem.ToString（）+''和s.mark =+ comboBox2.SelectedItem.ToString（）+ORDER BY newid（）;

SqlDataAdapter dataAdapter = new SqlDataAdapter（strSQL，con）;

SqlCommandBuilder commandBuilder = new SqlCommandBuilder（dataAdapter）;

$ b

//填充新数据表并将其绑定到BindingSource。

DataTable table = new DataTable（）;

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill（table）;

dbBindSource.DataSource = table;

//最后将数据绑定到网格

dataGridView1.DataSource = dbBindSource;

 b $ b

}

catch（例外）

{

MessageBox.Show（选择过滤器......）;

}

con.Close（）;

}

private void panel1_Paint（object sender，PaintEventArgs e）

{

}

private void button1_Click（object sender，EventArgs e）

{

//在数据库中保存

string a =;

con.Open（）;

SqlCommand c = new SqlComma nd（从QuestionPaper中删除，con）;

c.ExecuteNonQuery（）;

int i = 0;

while（dataGridView1。 RowCount> i）

{

cmd =新的SqlCommand（INSERT INTO QuestionPaper VALUES（+ dataGridView1.Rows [i] .HeaderCell.Value +，''+ dataGridView1 .Rows [i] .Cells [0] .Value +''），con）;

cmd.ExecuteNonQuery（）;

i ++;

}

SqlCommand c1 =新的SqlCommand （从QuestionPaper中删除，其中Qvalues =''+ a +''，con）;

c1.ExecuteNonQuery（）;

con.Close（）;

MessageBox.Show（成功提交）;

//// this.Close（）;

//创建Excel应用程序

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.A pplication（）;

//创建Excel应用程序

Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add（Type.Missing）;

//Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application（）;

Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet（）;

//Microsoft.Office.Interop.Excel .Workbook objWorkBook = null;

//查看程序背后的excel表

app.Visible = true;

//获取第一张纸的参考。默认情况下，它的名称是Sheet1。

//存储其对工作表的引用

objSheet =（Microsoft.Office.Interop.Excel.Worksheet ）workbook.Sheets [Sheet1];

objSheet =（Microsoft.Office.Interop.Excel.Worksheet）workbook.ActiveSheet;

//更改活动表格的名称

objSheet.Name =从gridview导出;

//在Excel中存储标题部分

//// for（int j = 1 ; j< dataGridView1.Columns.Count + 1; j ++）

//// {

//// objSheet.Cells [1，j] = dataGridView1.Columns [j - 1] .HeaderText;

////}

//将每行和每列值存储到excel表格

for（int k = 0; k< dataGridVie w1.Rows.Count - 1; k ++）

{

for（int l = 0; l< dataGridView1.Columns.Count; l ++）

$

objSheet.Cells [k + 1，l + 1] = dataGridView1.Rows [k] .Cells [l] .Value.ToString（）;

}

}

//保存应用程序

// workbook.SaveAs（@C：\\\ \\ _Book1.xml，Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange，Type.Missing， Type.Missing，Type.Missing，Type.Missing，Type.Missing）;

}

private void dataGridView1_DataBindingComplete（object sender，DataGridViewBindingCompleteEventArgs e）

{

if（null！= dataGridView1）

{

foreach（DataGridViewRow r in dataGridView1.Rows）

{

dataGridView1.Rows [r.Index] .HeaderCell.Value =（r.Index + 1）.ToString（）;

}

}

}

// private void button2_Click（object sender，EventArgs e）

// {

// //保存在数据库中

//字符串a =;

// con.Open（）;

// SqlCommand c = new SqlCommand（从QuestionPaper中删除，con）;

// c.ExecuteNonQuery（）;

// int i = 0;

// while（dataGridView1.RowCount> i）

// {

// cmd =新的SqlCommand（INSERT INTO QuestionPaper VALUES（+ dataGridView1.Rows [i] .HeaderCell.Value +，' '+ dataGridView1.Rows [i] .Cells [0] .Value +''），con）;

// cmd.ExecuteNonQuery（）;

// i ++;

//}

// SqlCommand c1 = new SqlCommand（从QuestionPaper中删除Qvalues =''+ a +''，con ）;

// c1.ExecuteNonQuery（）;

// con.Close（）;

// LittleFlower.ReportDocument reportdoc = new LittleFlower.ReportDocument（）;

// reportdoc.Show（）;

//} 

}

}

这是我的完整编码部分....此代码仍然在我的项目中运行....

I have a text file which is in tab deliminator and following is my code to generate its Excel.

protected void to_excel(object sender, EventArgs e)
    {
        string filepath = Path.Combine(Server.MapPath("~/Files"), fileupload.FileName);
        fileupload.SaveAs(filepath);
        string fname = fileupload.PostedFile.FileName;
        DataTable dt = (DataTable)ReadToEnd(filepath);
        string sFilename = fname.Substring(0, fname.IndexOf("."));
        HttpResponse response = HttpContext.Current.Response;
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=" + sFilename + ".xls");
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
        System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
        dg.DataSource = dt;
        dg.DataBind();
        dg.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
    private object ReadToEnd(string filePath)
    {
        DataTable dtDataSource = new DataTable();
        string[] fileContent = File.ReadAllLines(filePath);
        if (fileContent.Count() > 0)
        {
            string[] columns = fileContent[0].Split('\t');
            for (int i = 0; i < columns.Count(); i++)
            {
                dtDataSource.Columns.Add(columns[i]);
            }
            for (int i = 1; i < fileContent.Count(); i++)
            {
                string[] rowData = fileContent[i].Split('\t');
                dtDataSource.Rows.Add(rowData);
            }
        }
        return dtDataSource;
    }

This code works fine since i am generating 2003 excel file (.xls).

But if i am generating a 2007 (.xlsx) by changing the code to

Response.AddHeader("content-disposition", "attachment;filename=" + sFilename + ".xlsx");

i get an error like this http://s11.postimage.org/i2xt9tonn/error.jpg

I did my homework and came to know that this error is because the .xlsx file generated by my program is done by using HTML (markup language) XML (markup language) which should actually be done for a 2007 excel file.

My question is what changes should i do so that i get the desired result i.e. I get the 2007 excel sheet!!!

解决方案

You have to use extended libraries which I recommend using EPPlus which is a .net library that reads & writes Excel 2007/2010 files using the Open Office Xml format (xlsx). http://epplus.codeplex.com

and then replace the code

protected void to_excel(object sender, EventArgs e)
        {
            string filepath = Path.Combine(Server.MapPath("~/Files"), fileupload.FileName);
            fileupload.SaveAs(filepath);
            string fname = fileupload.PostedFile.FileName;
            DataTable dt = (DataTable)ReadToEnd(filepath);
            string sFilename = fname.Substring(0, fname.IndexOf("."));
            sFilename = sFilename + ".xlsx";
            MemoryStream ms = DataTableToExcelXlsx(dt, "Sheet1");
            ms.WriteTo(HttpContext.Current.Response.OutputStream);
            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + sFilename);
            HttpContext.Current.Response.StatusCode = 200;
            HttpContext.Current.Response.End();
        }

public void toexcel(DataTable dt, string Filename)
        {
            MemoryStream ms = DataTableToExcelXlsx(dt, "Sheet1");
            ms.WriteTo(HttpContext.Current.Response.OutputStream);
            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + Filename);
            HttpContext.Current.Response.StatusCode = 200;
            HttpContext.Current.Response.End();
        }
        public bool IsReusable
        {
            get { return false; }
        }
        public static MemoryStream DataTableToExcelXlsx(DataTable table, string sheetName)
        {
            MemoryStream Result = new MemoryStream();
            ExcelPackage pack = new ExcelPackage();
            ExcelWorksheet ws = pack.Workbook.Worksheets.Add(sheetName);
            int col = 1;
            int row = 1;
            foreach (DataColumn column in table.Columns)
            {
                ws.Cells[row, col].Value = column.ColumnName.ToString();
                col++;
            }
            col = 1;
            row = 2;
            foreach (DataRow rw in table.Rows)
            {
                foreach (DataColumn cl in table.Columns)
                {
                    if (rw[cl.ColumnName] != DBNull.Value)
                        ws.Cells[row, col].Value = rw[cl.ColumnName].ToString();
                    col++;
                }
                row++;
                col = 1;
            }
            pack.SaveAs(Result);
            return Result;
        }

I got this solution here http://forums.asp.net/p/1863741/5230401.aspx/1?Unable+to+generate+the+xlsx+file+

Try this coding in any button control ...this coding surely helpful for u....

 // creating Excel Application

 Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
 // creating Excel Application
 Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

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


 Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet();
 //Microsoft.Office.Interop.Excel.Workbook objWorkBook = null;


 // see the excel sheet behind the program

app.Visible = true;


// get the reference of first sheet. By default its name is Sheet1.

 // store its reference to worksheet
 objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
 objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;


 // changing the name of active sheet

 objSheet.Name = "Exported from gridview";


 // storing header part in Excel

 ////for (int j = 1; j < dataGridView1.Columns.Count + 1; j++)
 ////{

 ////    objSheet.Cells[1, j] = dataGridView1.Columns[j - 1].HeaderText;

 ////}


 // storing Each row and column value to excel sheet

 for (int k = 0; k < dataGridView1.Rows.Count - 1; k++)
 {

     for (int l = 0; l< dataGridView1.Columns.Count; l++)
     {

         objSheet.Cells[k + 1, l + 1] = dataGridView1.Rows[k].Cells[l].Value.ToString();

     }

 }



// save the application

// workbook.SaveAs(@"C:\\Book1.xml", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

 }

or try below link:

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(v=vs.80).aspx[^]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Office;

namespace Littleflower
{

public partial class Filter : Form
{
SqlCommand cmd;
SqlConnection con;
SqlDataAdapter da;
SqlDataReader dr;
DataSet ds;
//public enum XlSaveAsAccessMode
//public enum XlSaveConflictResolution
public Filter()
{
InitializeComponent();
con = new SqlConnection("Data Source=FABSYS27\\SQLEXPRESS; Initial Catalog=Questionare; User Id=sa; Pwd=saadmin");

}

private void Form1_Load(object sender, EventArgs e)
{

con.Open();
cmd = new SqlCommand("select distinct(subjectname) from subject", con);
dr = cmd.ExecuteReader();
comboBox1.Items.Clear();
while (dr.Read())
{
if (dr[0].ToString() != "")
{
comboBox1.Items.Add(dr[0].ToString());
}
}
dr.Close();
con.Close();

con.Open();
string a = "";
SqlCommand c = new SqlCommand("delete from questiontype where questions=''" + a + "''", con);
c.ExecuteNonQuery();
con.Close();
con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;

con.Close();

}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{

}

private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
{

}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

}

private void comboBox1_SelectedIndexChanged_1(object sender, EventArgs e)
{

con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid and s.subjectname=''" + comboBox1.SelectedItem.ToString() + "'' ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;

con.Close();

con.Open();
cmd = new SqlCommand("select distinct (topic) from subject where subjectname=''" + comboBox1.SelectedItem + "'' ", con);
dr = cmd.ExecuteReader();
comboBox3.Items.Clear();
while (dr.Read())
{
if (dr[0].ToString() != "")
{
comboBox3.Items.Add(dr[0].ToString());
}
}
dr.Close();
con.Close();

}

private void comboBox3_SelectedIndexChanged_1(object sender, EventArgs e)
{
con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid and s.subjectname=''" + comboBox1.SelectedItem.ToString() + "'' and s.topic=''" + comboBox3.SelectedItem.ToString() + "'' ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;

con.Close();
con.Open();
cmd = new SqlCommand("select distinct (mark) from subject where subjectname=''"+comboBox1.SelectedItem+"'' and topic=''"+comboBox3.SelectedItem+"''", con);
dr = cmd.ExecuteReader();
comboBox2.Items.Clear();
while (dr.Read())
{
if (dr[0].ToString() != "")
{
comboBox2.Items.Add(dr[0].ToString());
}
}
dr.Close();

con.Close();

}

private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
con.Open();
String strSQL = "select q.questions from subject s ,questiontype q where q.subjectid=s.subjectid and s.subjectname=''" + comboBox1.SelectedItem.ToString() + "''and s.topic=''" + comboBox3.SelectedItem.ToString() + "'' and s.mark=" + comboBox2.SelectedItem.ToString() + " ";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;

con.Close();

}

private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
{
con.Open();
try
{

String strSQL = "SELECT TOP " + comboBox4.SelectedItem + " q.questions FROM questiontype q, subject s where q.subjectid=s.subjectid and s.subjectname=''" + comboBox1.SelectedItem.ToString() + "''and s.topic=''" + comboBox3.SelectedItem.ToString() + "'' and s.mark=" + comboBox2.SelectedItem.ToString() + " ORDER BY newid()";

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, con);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

// finally bind the data to the grid

dataGridView1.DataSource = dbBindSource;

}
catch (Exception)
{
MessageBox.Show("Select filters...");
}
con.Close();
}

private void panel1_Paint(object sender, PaintEventArgs e)
{

}

private void button1_Click(object sender, EventArgs e)
{
// Saving in database

string a = "";
con.Open();
SqlCommand c = new SqlCommand("delete from QuestionPaper", con);
c.ExecuteNonQuery();
int i = 0;
while (dataGridView1.RowCount > i)
{
cmd = new SqlCommand("INSERT INTO QuestionPaper VALUES(" + dataGridView1.Rows[i].HeaderCell.Value + ",''" + dataGridView1.Rows[i].Cells[0].Value + "'')", con);
cmd.ExecuteNonQuery();

i++;

}
SqlCommand c1 = new SqlCommand("delete from QuestionPaper where Qvalues=''"+a+"''", con);
c1.ExecuteNonQuery();
con.Close();
MessageBox.Show("Submitted Successfully");
////this.Close();

// creating Excel Application

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating Excel Application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

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

Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet();
//Microsoft.Office.Interop.Excel.Workbook objWorkBook = null;

// see the excel sheet behind the program

app.Visible = true;

// get the reference of first sheet. By default its name is Sheet1.

// store its reference to worksheet
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;

// changing the name of active sheet

objSheet.Name = "Exported from gridview";

// storing header part in Excel

////for (int j = 1; j < dataGridView1.Columns.Count + 1; j++)
////{

//// objSheet.Cells[1, j] = dataGridView1.Columns[j - 1].HeaderText;

////}

// storing Each row and column value to excel sheet

for (int k = 0; k < dataGridView1.Rows.Count - 1; k++)
{

for (int l = 0; l< dataGridView1.Columns.Count; l++)
{

objSheet.Cells[k + 1, l + 1] = dataGridView1.Rows[k].Cells[l].Value.ToString();

}

}

// save the application

// workbook.SaveAs(@"C:\\Book1.xml", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}

private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{

if (null != dataGridView1)
{
foreach (DataGridViewRow r in dataGridView1.Rows)
{
dataGridView1.Rows[r.Index].HeaderCell.Value = (r.Index + 1).ToString();
}
}
}

// private void button2_Click(object sender, EventArgs e)
// {
// // Saving in database

// string a = "";
// con.Open();
// SqlCommand c = new SqlCommand("delete from QuestionPaper", con);
// c.ExecuteNonQuery();
// int i = 0;
// while (dataGridView1.RowCount > i)
// {
// cmd = new SqlCommand("INSERT INTO QuestionPaper VALUES(" + dataGridView1.Rows[i].HeaderCell.Value + ",''" + dataGridView1.Rows[i].Cells[0].Value + "'')", con);
// cmd.ExecuteNonQuery();
// i++;
// }
//SqlCommand c1 = new SqlCommand("delete from QuestionPaper where Qvalues=''" + a + "''", con);
// c1.ExecuteNonQuery();
// con.Close();

// LittleFlower.ReportDocument reportdoc = new LittleFlower.ReportDocument();
// reportdoc.Show();

// }

}

}

this my full coding part....this code still running in my project....

这篇关于无法生成.xlsx文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

无法生成.xlsx文件 [英] Unable to generate the .xlsx file

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

无法生成.xlsx文件 [英] Unable to generate the .xlsx file

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭