EXCEL:利用EXCEL对数据进行格式转换

来源：互联网发布：java 策略模式应用编辑：程序博客网时间：2024/05/27 09:45

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server">    <title>读取文本文件到Excel并对数据列进行格式化</title>    <style type="text/css">        .style1        {            border: medium double #E7E7E7;            font-size: 10pt;        }    </style></head><body style="font-size: 10pt">    <form id="form1" runat="server">    <div>        <table class="style1">            <tr>                <td>                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">                    <Columns>                        <asp:BoundField DataField="EName" HeaderText="姓名" />                        <asp:BoundField DataField="ESex" HeaderText="性别" />                        <asp:BoundField DataField="EAge" HeaderText="年龄" />                        <asp:BoundField DataField="EPlace" HeaderText="籍贯" />                        <asp:BoundField DataField="EMoney" HeaderText="工资" />                    </Columns>                    </asp:GridView>                </td>            </tr>            <tr>                <td align="center">                    <asp:Button ID="btnExport" runat="server" Text="Excel格式转换"                         onclick="btnExport_Click" />                </td>            </tr>        </table>    </div>    </form></body></html>

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using Word = Microsoft.Office.Interop.Word;using System.Threading;using office = Microsoft.Office.Core;using System.Reflection;using System.IO;using System.Text.RegularExpressions;using System.Text;using System.Data.SqlClient;using System.Configuration;public partial class _Default : System.Web.UI.Page {    protected void Page_Load(object sender, EventArgs e)    {        if (!IsPostBack)        {            string conStr = ConfigurationManager.ConnectionStrings["conStr"].ToString();            using (SqlConnection con = new SqlConnection(conStr))            {                con.Open();                SqlCommand cmd = new SqlCommand("select * from tb_Employee", con);                SqlDataReader sdr = cmd.ExecuteReader();                GridView1.DataSource = sdr;                GridView1.DataBind();            }        }    }    protected void btnExport_Click(object sender, EventArgs e)    {        if (GridView1.Rows.Count == 0)//判断是否有数据            return;//返回        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象        excel.Application.Workbooks.Add(true);//在Excel中添加一个工作簿        excel.Visible = true;//设置Excel显示        //生成字段名称        for (int i = 0; i < GridView1.Columns.Count; i++)        {            excel.Cells[1, i + 1] = GridView1.Columns[i].HeaderText;//将数据表格控件中的列表头填充到Excel中        }        //填充数据        for (int i = 0; i < GridView1.Rows.Count; i++)//遍历数据表格控件的所有行        {            for (int j = 0; j < GridView1.Columns.Count; j++)//遍历数据表格控件的所有列            {                if (j ==3)                {                    excel.Cells[i + 2, j + 1] = "“" + GridView1.Rows[i].Cells[j].Text.Trim() + "”";//格式化为字符串类型并填充Excel表格                }                else if (j ==4)                {                    excel.Cells[i + 2, j + 1] = Convert.ToDouble(GridView1.Rows[i].Cells[j].Text).ToString("#,##0.00");//格式化为双精度浮点类型并填充Excel表格                }                else                {                    excel.Cells[i + 2, j + 1] = GridView1.Rows[i].Cells[j].Text;//填充Excel表格                }            }        }    }}

0 0