110,566
社区成员
发帖
与我相关
我的任务
分享
DataTable dt = new DataTable();
dt.Columns.Add("起始时间");
dt.Columns.Add("主办人");
dt.Columns.Add("事项部门");
dt.Columns.Add("事项类型");
dt.Columns.Add("事项说明");
dt.Columns.Add("联系信息");
dt.Columns.Add("联系人");
dt.Columns.Add("接收方式");
dt.Columns.Add("处理反馈");
dt.Columns.Add("完成时间");
dt.Columns.Add("用时");
dt.Columns.Add("补充");
dt.Columns.Add("更新时间");
dt.Columns.Add("更新人");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
dt.Rows.Add(GridView1.Rows[i]);
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
private bool exportexcelxlsx(string fname, DataTable tbl)
{
try
{
string fn = Path.GetFileNameWithoutExtension(fname);
IWorkbook workbook = new XSSFWorkbook();
ISheet worksheet = workbook.CreateSheet(fn);
#region 右击文件 属性信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "test";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = uivariable.prms_user.u_name; //填加xls文件作者信息
si.ApplicationName = "test EXPORT"; //填加xls文件创建程序信息
si.LastAuthor = uivariable.prms_user.u_name; //填加xls文件最后保存者信息
si.Comments = "test"; //填加xls文件作者信息
si.Title = "test EXPORT"; //填加xls文件标题信息
si.Subject = "test EXPORT"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int row = tbl.Rows.Count;
int col = tbl.Columns.Count;
IRow rxls;
ICell cell;
string title = string.Empty;
//表头
int addrow = 0;
rxls = worksheet.CreateRow(addrow++);
cell = rxls.CreateCell(0);
cell.SetCellValue(title);
rxls = worksheet.CreateRow(addrow++);
cell = rxls.CreateCell(0);
title = "Date : " + DateTime.Now.ToString("yyyy-MM-dd");
cell.SetCellValue(title);
//获取列名称
for (int r = 0; r < col; r++)
{
if (tbl.Columns[r].ColumnMapping != MappingType.Hidden)
{
cell = rxls.CreateCell(r);
string colname = tbl.Columns[r].ColumnName;
cell.SetCellValue(colname);
}
}
//写入数据
string cellval = string.Empty;
for (int i = 0; i < row; i++)
{
rxls = worksheet.CreateRow(i + addrow);
for (int j = 0; j < col; j++)
{
if (tbl.Columns[j].ColumnMapping != MappingType.Hidden)
{
cell = rxls.CreateCell(j);
cellval = tbl.Rows[i][j].ToString();
cell.SetCellValue(cellval);
}
}
}
//页脚
int ftr = col - 4;
if (ftr < 0)
{
ftr = 1;
}
addrow++;
rxls = worksheet.CreateRow(row + addrow);
cell = rxls.CreateCell(ftr);
cellval = "导出日期: " + DateTime.Now.ToString();
cell.SetCellValue(cellval);
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fname, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
string msg = "数据已经成功导出.\n\r" + fname;
MessageBox.Show("数据已经成功导出");
return true;
}
catch (Exception ex)
{
string msg = "数据导出失败." + ex.ToString();
MessageBox.Show("数据导出失败");
return false;
}
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < GridView1.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(GridView1.Rows[i].Cells[j].ToString());
}
}
.....第二个循环写错了,sorry,
应该是取列
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("起始时间");
row.CreateCell(1).SetCellValue("主办人");
row.CreateCell(2).SetCellValue("事项部门");
row.CreateCell(3).SetCellValue("事项类型");
row.CreateCell(4).SetCellValue("事项说明");
row.CreateCell(5).SetCellValue("联系信息");
row.CreateCell(6).SetCellValue("联系人");
row.CreateCell(7).SetCellValue("接收方式");
row.CreateCell(8).SetCellValue("处理反馈");
row.CreateCell(9).SetCellValue("完成时间");
row.CreateCell(10).SetCellValue("用时");
row.CreateCell(11).SetCellValue("补充");
row.CreateCell(12).SetCellValue("更新时间");
row.CreateCell(13).SetCellValue("更新人");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < GridView1.Rows.Count; j++)
{
row2.CreateCell(j).SetCellValue(GridView1.Rows[i].Cells[j].ToString());
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();