将数据从gridview导出到Excel工作表，其格式与在网格视图中完成的格式相同 [英] Export data from gridview to Excel sheet with the same formatting as done in grid view

问题描述

我想将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屋！