安装npoi,下面是具体的C#代码:
public static XSSFWorkbook BuildWorkbook(DataTable dt)
{
var book = new XSSFWorkbook();
ISheet sheet = book.CreateSheet("Sheet1");
IRow first_drow = sheet.CreateRow(); string import_title = ConfigurationManager.AppSettings["import_title"];
if (!import_title.IsEmpty())
{
string[] temps = import_title.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = ; i < temps.Length; i++)
{
ICell cell = first_drow.CreateCell(i, CellType.String);
cell.SetCellValue(temps[i]);
}
}
//Data Rows
int index = ;
for (int i = ; i <= dt.Rows.Count; i++)
{
IRow drow = sheet.CreateRow(i);
for (int j = ; j < dt.Columns.Count; j++)
{
ICell cell = drow.CreateCell(j, CellType.String);
cell.SetCellValue(dt.Rows[index][j].ToString());
}
index++;
}
//自动列宽
for (int i = ; i <= dt.Columns.Count; i++)
sheet.AutoSizeColumn(i, true); return book;
} public static void ExportExcel(string idcard_no, string name, string fileName = "ExamInfoExcel")
{
//生成Excel
IWorkbook book = BuildWorkbook(ExcelData(idcard_no, name)); //web 下载
if (fileName == "")
fileName = string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now);
fileName = fileName.Trim();
string ext = Path.GetExtension(fileName); if (ext.ToLower() == ".xls" || ext.ToLower() == ".xlsx")
fileName = fileName.Replace(ext, string.Empty); HttpResponse httpResponse = HttpContext.Current.Response;
httpResponse.Clear();
httpResponse.Buffer = true;
httpResponse.Charset = Encoding.UTF8.BodyName;
//Remarks:xls是03版excel所用格式,xlsx是07版所用格式,这里默认导出07版的,如果电脑上是03版打不开这个文件,把后缀名xlsx改成xls即可。
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
httpResponse.ContentEncoding = Encoding.UTF8;
httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";
book.Write(httpResponse.OutputStream);
httpResponse.End();
}
public static DataTable ExcelData(string idcard_no, string name)
{
string where = " 1=1 ";
if (!string.IsNullOrEmpty(idcard_no))
{
idcard_no = idcard_no.TrimStart().TrimEnd();
where += " and idcard_no like '%" + idcard_no + "%'";
}
if (!string.IsNullOrEmpty(name))
{
idcard_no = idcard_no.TrimStart().TrimEnd();
name = name.TrimStart().TrimEnd();
where += " and name like '%" + name + "%'";
}
string import_files = ConfigurationManager.AppSettings["import_files"];
string sql = string.Format(@"SELECT {0} FROM ExamInfo where {1} ORDER BY id", import_files, where);
using (var connection = ConnectionFactory.CreateSqlConnection())
{
DataTable dt = new DataTable();
dt.Load(connection.ExecuteReader(sql));
return dt;
}
}
控制器层调用如下:
public void ExcelImport(string idcard_no, string name)
{
Application.ExportExcel(idcard_no, name, "ExamInfoExcel");
}
JQ页面调用如下:
$("#import").click(function () {
var href = "/Home/ExcelImport";
var idcard_no = $("#IDNumber").val();
var name = $("#username").val();
href += "?idcard_no=" + idcard_no + "&name=" + name;
$("#import_a").attr("href", href);
$("#download").click();
});
html:
<button class="layui-btn" id="import">导出数据</button>
<a href="/Home/ExcelImport" rel="external nofollow" id="import_a" style="display:none;" ><span id="download">隐藏的导出下载地址</span></a>
页面之所以在按钮的基础上加了一个隐藏的a标签,原因在于通过window.open打开的地址容易被浏览器拦截,而通过模拟触发a标签进行点击的则不会。
关于配置,需要导出的列以及excel的表头我控制在了webconfig里:
!–导出配置–>
<add key=”import_title” value=”序号,姓名”/>
<add key=”import_files” value=”id,name”/>
以上,是一个较为完整的导出数据到excel示例。