C# 在Gridview中 对某一行的数据，单独进行处理，使用NPOI 导出excel 并且调整Excel的 样式
前台 Gridview 中
-----------------------------------------------------
<asp:GridView ID="gridView" runat="server" AutoGenerateColumns="False" Width="100%"
AllowSorting="True" CellPadding="5" BorderWidth="1px" PageSize="20" DataKeyNames="Id"
OnSorting="GridViewSorting" OnRowCommand="gridView_RowCommand"
OnRowDataBound="GridView_RowDataBound">
<asp:TemplateField HeaderText="签到表下载" ItemStyle-Width="100px">
<ItemTemplate>
<asp:Button ID="ReviewResultsDownload" runat="server" CommandName="ReviewResultsDownload" Font-Size="smaller"
CommandArgument='<%#Eval("Id") %>' Text="签到表下载" />
</ItemTemplate>
<ControlStyle CssClass="buttonText" />
</asp:TemplateField>
</asp:GridView>
----------------------------------------------------------------
后台
----------------------------------------------------------------------
导入NPOI
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Reflection;
using NPOI.SS.Util;
using System.IO;
using GX.Common;
-------------------------------------------------------------------------
protected void gridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
Session["Category"] = ViewState["Category"];
string id =e.CommandArgument.ToString().Trim();
if (e.CommandName == "ReviewResultsDownload")
{
string ids = e.CommandArgument.ToString().Trim();
ViewData data = BindViewData();
DataSet ds = bll.GetListByPage(data);
DataRow[] drArr = ds.Tables[0].Select("id='" + ids + "'");
System.Data.DataTable dtNew = ds.Tables[0].Clone();
for (int i = 0; i < drArr.Length; i++)
{
dtNew.ImportRow(drArr[i]);
}
string name = "";
string Tname = dtNew.Rows[0]["ReviewName"].ToString();
name = clFielName(Tname + "_" + DateTime.Now.ToString("yyyyMMddHHmmss"));
string a = gridView.Rows[0].Cells[1].Text;
createSheet( ids, name, Tname);
}
｝
------------------------------------------------------------
使用NPOI 创建excel 表的方法
private void createSheet(string id, string name, string Tname)
{
//获得当前gridview上的所有id
//int excelDataLength = GridView1.DataKeys.Count;//获取当前gridview上有多少id
//int[] lengthArray = new int[excelDataLength];//数组用来保存gridview上的id
//for (int i = 0; i < excelDataLength; i++)
//{
// lengthArray[i] = int.Parse(GridView1.DataKeys[i].Value.ToString());
//}
//去数据库取得对应id数据
//将取得数据赋给excel生成
Pact.BLL.DutyReview.DR_ReviewInfo bll = new BLL.DutyReview.DR_ReviewInfo();
DataSet ds = bll.GettheList(id);//根据ID 查询数据库
if (ds.Tables[0].Rows.Count < 1)
{
MessageBox.Show(this, "暂无签到表数据！");
return;
}
int excelDataLength = ds.Tables[0].Rows.Count + 1;
//int[] lengthArray = new int[excelDataLength];//数组用来保存gridview上的id
//for (int i = 0; i < excelDataLength; i++)
//{
// lengthArray[i] = int.Parse(ds.Tables[0].Rows[i]["EmpCode"].ToString());
//}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheetOne = (HSSFSheet)workbook.CreateSheet("Sheet1");//获取excel第一张表
for (int i = 0; i <= excelDataLength; i++)//建立所有行
{
sheetOne.CreateRow(i);
}
//建立第一行表头
HSSFRow rowOne = (HSSFRow)sheetOne.GetRow(0);
for (int i = 0; i < 29; i++)
{
rowOne.CreateCell(i);
}
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
bodyStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
bodyStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
bodyStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.FontName = "宋体";
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
sheetOne.CreateRow(0).Height = 100;
sheetOne.CreateRow(0).HeightInPoints = 30;
sheetOne.DefaultColumnWidth = 35;
sheetOne.DefaultRowHeight = 230;
cellStyle.SetFont(font);
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.Alignment = HorizontalAlignment.CenterSelection;
ICell cell = workbook.GetSheet("Sheet1").CreateRow(0).CreateCell(0);
cell.CellStyle = cellStyle;
rowOne.Cells[0].SetCellValue("【 " + Tname + " 】签到表"); //----------------这个Excel表的标题
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);// -----------这是合并 Excel 第一行 从0到3 的单元格
rowOne.Cells[0].CellStyle = cellStyle;//设置
rowOne.Sheet.AddMergedRegion(region);
HSSFRow rowOne1 = (HSSFRow)sheetOne.GetRow(1);
for (int i = 0; i < 29; i++)
{
rowOne1.CreateCell(i);
}
rowOne1.CreateCell(1);
rowOne1.Cells[0].SetCellValue("序号");
rowOne1.Cells[1].SetCellValue("评审人工号");
rowOne1.Cells[2].SetCellValue("评审人姓名");
rowOne1.Cells[3].SetCellValue("签到时间");
NPOI.SS.UserModel.IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 14;
font1.FontName = "宋体";
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.SetFont(font1);
//cellStyle1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey40Percent.Index;
//cellStyle1.FillPattern = FillPattern.SolidForeground;
//cellStyle1.Alignment = HorizontalAlignment.CenterSelection;
//ICell cell1 = workbook.GetSheet("Sheet1").CreateRow(1).CreateCell(0);
//cell1.CellStyle = cellStyle1;
rowOne1.Cells[0].CellStyle = cellStyle1;//设置
rowOne1.Cells[1].CellStyle = cellStyle1;//设置
rowOne1.Cells[2].CellStyle = cellStyle1;//设置
rowOne1.Cells[3].CellStyle = cellStyle1;//设置
HSSFRow rowOne2 = (HSSFRow)sheetOne.GetRow(2);
for (int i = 0; i < 29; i++)
{
rowOne2.CreateCell(i);
}
for (int i = 2; i <= excelDataLength; i++)
{
NPOI.SS.UserModel.IFont font2 = workbook.CreateFont();
font2.FontHeightInPoints = 12;
font2.FontName = "宋体";
ICellStyle cellStyle2 = workbook.CreateCellStyle();
cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.SetFont(font2);
int dsid = Convert.ToInt32(ds.Tables[0].Rows[i - 2]["id"].ToString());
int a = Convert.ToInt32(id);
Pact.Model.DutyReview.DR_ReviewInfo modelToExcel = bll.GettheModel(dsid);//循环遍历数组，依次得到对象
HSSFRow excelRow = (HSSFRow)sheetOne.GetRow(i);//将对象的属性依次赋给excel的单元格
excelRow = CreateRowCells(excelRow, 4);
string num = Convert.ToString(i - 1);
excelRow.Cells[0].SetCellValue(num);
excelRow.Cells[1].SetCellValue(modelToExcel.EmpCode);
excelRow.Cells[2].SetCellValue(modelToExcel.EmpName);
excelRow.Cells[3].SetCellValue(modelToExcel.SignInTime);
excelRow.Cells[0].CellStyle = cellStyle2;//设置
excelRow.Cells[1].CellStyle = cellStyle2;//设置
excelRow.Cells[2].CellStyle = cellStyle2;//设置
excelRow.Cells[3].CellStyle = cellStyle2;//设置
}
string path = Server.MapPath("~/DutyReview/checkExcel/try.xls");
using (FileStream stream = new FileStream(path, FileMode.Create))
{
workbook.Write(stream);
}
//excel生成完毕，提供下载
string fileName = "" + name + ".xls";//客户端保存的文件名
//string filePath = Server.MapPath("DownLoad/aaa.txt");//路径
string filePath = path;
FileInfo fileInfo = new FileInfo(filePath);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(fileInfo.FullName);
Response.Flush();
Response.End();
File.Delete(fileName);
}
