.NET导出Gridview到excel 带模板列显示
界面内容如下：导出后显示查询到的数据如下：
c#调用代码如下：
protected void btnOutput_Click(object sender, EventArgs e)
{
gvEquData.AllowPaging = false;
BindGridViewData();
ExcelHelper helper = new ExcelHelper();
helper.ExportExcel(gvEquData, "设备状态信息列表"+DateTime.Now.ToString("yyyyMMddHHmmss")+".xls", "设备状态信息列表");
}
这里我使用了NPOI这个dll来操作excel，这个dll需要去网上下载。然后新建一个类用来操作excel，如下：
public class ExcelHelper
{
#region NPOI Excel导出
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="GV">控件名称(GridView) 如有需要稍加修改可应用于DateGird等.Net数据控件</param>
/// <param name="ExcleName">保存的Excel名字</param>
/// <param name="SheetName">工作簿名字</param>
/// <param name="cols">图片列 如果没有图片列 该参数可赋 NULL </param>
public void ExportExcel(GridView GV, string ExcleName, string SheetName)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
InitializeWorkbook(hssfworkbook, "雄帝", " Export Excel ");
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet(SheetName);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();//插入图片所用
HSSFRow row;
HSSFCell cell;
//合并单元格信息
int startRow = 0;
int startColumn = 0;
int span = 0;
int col = 0;
//当前的格数
int rownum = 0;
row = (HSSFRow)sheet1.CreateRow(0);
//添加Excel标题
for (int K = 0; K < GV.HeaderRow.Cells.Count; K++)//GV.Columns.Count
{
cell = (HSSFCell)row.CreateCell(K);
if (GV.HeaderRow.Cells[K].HasControls())
{
ControlCollection cc=GV.HeaderRow.Cells[K].Controls;
if (cc.Count < 2)
{
if (cc[0] is Literal)
{
Literal ltl = cc[0] as Literal;
cell.SetCellValue(ltl.Text);
}
else
{
cell.SetCellValue(GV.Columns[K].HeaderText);
}
}
else
{
if (cc[1] is Literal)
{
Literal ltl = cc[1] as Literal;
cell.SetCellValue(ltl.Text);
}
else
{
cell.SetCellValue(GV.Columns[K].HeaderText);
}
}
}
else
{
cell.SetCellValue(GV.Columns[K].HeaderText);//
}
//cell.SetCellValue(getCellText(GV.HeaderRow.Cells[K]));//
}
//加载数据
for (int i = 0; i < GV.Rows.Count; i++)//
{
row = (HSSFRow)sheet1.CreateRow(i + 1);
rownum = i + 1;
for (int j = 0; j < GV.HeaderRow.Cells.Count; j++)//GV.Columns.Count
{
if (GV.HeaderRow.Cells[j].Controls.Count>1)
{
cell = (HSSFCell)row.CreateCell(j);
if (GV.HeaderRow.Cells[j].Controls[0] is CheckBox)
{
CheckBox cbx = GV.HeaderRow.Cells[j].Controls[0] as CheckBox;
if (cbx.Checked)
{
cell.SetCellValue("是");
}
else
{
cell.SetCellValue("否");
}
}
}
else
{
TableCell Usecell = GV.Rows[i].Cells[j];
if (Usecell.RowSpan != 0 || Usecell.ColumnSpan != 0)//当含有和并列(行)的时候记录该合并数据
{
startRow = i + 1;//起始行
startColumn = j;//起始列
span = Usecell.RowSpan;//合并的行数
col = Usecell.ColumnSpan;//合并的列数
}
cell = (HSSFCell)row.CreateCell(j);
//当处于合并状时忽略该格式内容
if (i + 1 > startRow && j > startColumn && (startRow + span) > i + 1 && (startColumn + col) > j)
{
}
else if (i + 1 == startRow && j == startColumn)
{
//进行单元格的合并
int row2 = (span == 0) ? 0 : (span - 1);
int col2 = (col == 0) ? 0 : (col - 1);
sheet1.AddMergedRegion(new Region(i + 1, j, i + row2 + 1, j + col2));
cell.SetCellValue(getCellText(GV.Rows[i].Cells[j]));
}
else
{
cell.SetCellValue(getCellText(GV.Rows[i].Cells[j]));
}
}
}
}
//加载Footer部分数据
row = (HSSFRow)sheet1.CreateRow(rownum + 1);
int footerAt = 0;
int footSpan = 0;
if (GV.FooterRow != null)
{
for (int footNum = 0; footNum < GV.FooterRow.Cells.Count; footNum++)
{
TableCell footTc = GV.FooterRow.Cells[footNum];
if (footTc.ColumnSpan != 0)
{
footSpan = footTc.ColumnSpan;
footerAt = footNum;
}
cell = (HSSFCell)row.CreateCell(footNum);
if (footNum > footerAt && footNum < footSpan + footerAt)
{
}
else if (footNum == footerAt)//合并单元格
{
int footercol2 = (footSpan == 0) ? 0 : (footSpan - 1);
sheet1.AddMergedRegion(new Region(rownum + 1, footerAt, rownum + 1, footerAt + footercol2));
cell.SetCellValue(getCellText(GV.FooterRow.Cells[footNum]));
}
else
{
cell.SetCellValue(getCellText(footTc));
}
}
}
string path = ExcleName;
ExportToExcel(hssfworkbook, ExcleName);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="Dt">数据源</param>
/// <param name="ExcleName">导入文件名称</param>
/// <param name="SheetName">工作薄名称</param>
/// <param name="titleArr">标题栏</param>
/// <param name="clumnArr">栏位名</param>
public void ExportExcel(DataTable Dt, string ExcleName, string SheetName, string[] titleArr, string[] clumnArr)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
InitializeWorkbook(hssfworkbook, "雄帝", " Export Excel ");
HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet(SheetName);
int rowCount = 0;
HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0);
rowCount++;
//循环写出列头
for (int i = 0; i < titleArr.Le补充：Web开发 , ASP.Net ,
