iTextSharp EXCEL输出 [英] iTextSharp EXCEL output

问题描述

我使用以下代码将数据库中的许多表格中的数据输出到Excel文档。

I am using the following code to output data from a number of tables in my database to an excel document.

Protected void btnExcelExport_Click(object sender, EventArgs e)
{

string strQuery = "SELECT s.Story, s.StoryCategoryID, CONVERT(VARCHAR(10), 
s.CreationDate, 103) AS CreationDate, m.CompanyRole, af.Name FROM Story s INNER JOIN    
ProjectIterationMember pm ON pm.ProjectIterationMemberID = s.ProjectIterationMemberID 
INNER JOIN Iterations i ON i.ProjectIterationID = pm.ProjectIterationID INNER JOIN 
Member m ON m.MemberID = pm.MemberID INNER JOIN ProjectStoryFactors psf ON psf.StoryID = 
s.StoryID INNER JOIN AgileFactors af ON af.AgileFactorID = psf.AgileFactorID WHERE 
i.ProjectID = '" + proj_id + "'";

SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);

GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();

Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition","attachment;filename=RetroCloud" +  
DateTime.Now.Ticks + ".xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);

for (int i = 0; i < GridView1.Rows.Count; i++)
{

   GridView1.Rows[i].Attributes.Add("class", "textmode");
}
GridView1.RenderControl(hw);

string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}

输出如下：

Story | StoryCategoryID | CreationDate | CompanyRole | Name

negative iii | 1 | 21/02/2011 | Business Analyst | Project Duration
negative iii | 1 | 21/02/2011 | Business Analyst | Team Size
negative iii | 1 | 21/02/2011 | Business Analyst | Process
negative ccc | 1 | 22/02/2011 | Admin | Workspace Layout
negative ccc | 1 | 22/02/2011 | Admin | Organisational and Reporting Structure
negative ccc | 1 | 22/02/2011 | Admin | Process

我想做的是3件事。非常感谢您的帮助！

What I would like to do is 3 things. Many thanks for your help!

1）更改明显来自数据库字段的标题名称（即从StoryCategoryID到Story Type），
2 ）对于StoryCategoryID，如果检索到1 - 显示否定，如果检索到0，则显示正。
3）输出格式如下：

1) Change the header names which are clearly coming from the database fields (ie. from StoryCategoryID to Story Type), 2) For StoryCategoryID, if 1 is retrieved - display 'Negative' and if 0 is retrieved - display 'Positive' instead. 3) Have the output in the following format:

Story | Story Type | Creation Date | Company Role | Tag 1 | Tag 2 | Tag 3

negative iii | 1 | 21/02/2011 | Business Analyst | Project Duration | Team Size | Process
negative ccc | 1 | 22/02/2011 | Admin | Workspace Layout | Organisational | Process

推荐答案

您可以修改数据表的列名。使用 foreach（dt.Columns中的DataColumn col）然后使用 col.ColumnName

You can modify the column names of the data table. Use foreach(DataColumn col in dt.Columns) then use col.ColumnName

您可以使用（Convert.ToInt16（col [n]）！= 1）访问和修改标准的特定列的数据？循环中的肯定：否定。使用 dt.Rows.Count 获取循环条件。

You can access and modify the data for the specific column for your criteria using (Convert.ToInt16(col[n]) != 1)? "Positive" : "Negative" inside a loop. Use dt.Rows.Count for the loop's condition.

希望这会有所帮助。

你可以在 foreach 循环中使用它：

You can use this inside the foreach loop:

if (col.ColumnName == "StoryCategoryId") //Or "Story Type"
for(int rowCount = 0; rowCount < table.Rows.Count; rowCount++)
{
    col[rowCount] = (Convert.ToInt16(col[rowCount]) == 0) ? "Positive" : "Negative";
}

这篇关于iTextSharp EXCEL输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！