将数据从gridview导出到Excel工作表,其格式与在网格视图中完成的格式相同 [英] Export data from gridview to Excel sheet with the same formatting as done in grid view
本文介绍了将数据从gridview导出到Excel工作表,其格式与在网格视图中完成的格式相同的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将gridview数据导入excelsheet,但导入的数据会丢失gridview中完成的格式化。我该如何解决这个问题呢?
我的代码是:
Hi,
I am tring to import the gridview data to excelsheet but the imported data losses the formatting done in the gridview. How can I solve this problem?
My code is:
public partial class AddingGridDataInExcel : System.Web.UI.Page
{
string fileLoc;
string result;
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\zid120\Timesheet\eTimeTrackLite1.mdb;");
OleDbDataAdapter oda = new OleDbDataAdapter("select distinct e.EmployeeName,a.AttendanceDate,a.PunchRecords from Employees as e inner join AttendanceLogs as a on e.EmployeeId=a.EmployeeId where e.RecordStatus=1 and e.EmployeeName not like '%[0-9]%' and (a.AttendanceDate >= #1/1/2013#) and (a.AttendanceDate <= #7/30/2013#)", oconn);
oda.Fill(ds);
System.Web.UI.WebControls.Style myStyle = new System.Web.UI.WebControls.Style();
myStyle.ForeColor = System.Drawing.Color.Red;
myStyle.BackColor = System.Drawing.Color.Yellow;
MyTable.ApplyStyle(myStyle);
MyTable.DataSource = ds;
MyTable.DataBind();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
Application xlApp = new Application();
Workbook newWorkbook = xlApp.Application.Workbooks.Add();
Worksheet newWorksheet = newWorkbook.Sheets.Add();
newWorksheet.Name = "19Aug2013"; //***** Same as workbook name
System.Data.DataTable dt = (System.Data.DataTable)((DataSet)MyTable.DataSource).Tables[0];
int columnCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
int excelRowPosition = 0;
int excelColumnPosition = 0;
for (int row = 0; row < rowCount; row++)
{
for (int column = 0; column < columnCount; column++)
{
string valueToInsert = dt.Rows[row][column].ToString();
excelRowPosition = row + 1; excelColumnPosition = column + 1;
newWorksheet.Cells[excelRowPosition, excelColumnPosition] = valueToInsert;
}
}
newWorkbook.SaveAs(@"E:\Shweta.xlsx");
newWorkbook.Close(true);
xlApp.Quit();
}
推荐答案
将您用于gridview的样式写入Response.Write(样式) ;
write the styles that you used for gridview like below to Response.Write(style);
string style = @"<style>.ui-widget-header {
border: 1px solid #4297d7;
background-color: #3B5998;
color: #ffffff;
font-weight: bold;
}
.ui-dialog-content {
position: relative;
border: 0;
padding: .5em 1em;
background: none;
overflow: auto;
background:#ffffff;
}</style>
";
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=DataCleaning.xls");
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
grddataCleaning.RenderControl(htw);
Response.Write(style);
Response.Write(sw.ToString());
Response.End();
这篇关于将数据从gridview导出到Excel工作表,其格式与在网格视图中完成的格式相同的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文