if (Literal1.Text.Trim() == "") {
Literal1.Text = "1=1";
}
string Sql = @"SELECT user_name AS '姓名'
,user_sex AS '性别'
,identity_number AS '身份证号'
,user_education AS '学历'
,user_political AS '政治面貌'
,REPLACE(ISNULL(return_serve,''),'请选择','非党员') AS '是否有回村任职意愿'
,REPLACE(ISNULL(user_county,'')+ISNULL(user_towns,'')+ISNULL(user_village,''),'请选择','') AS '籍贯'
,REPLACE(ISNULL(B.name,'')+ISNULL(C.name,'')+ISNULL(D.name,''),'请选择','') AS '工作地'
,job_unit AS '工作单位'
,job_unit_nature AS '工作单位性质'
,job_postion AS '职务'
,job_title AS '职称'
,return_bus AS '回乡就业创业意愿'
,return_project AS '计划返乡创业项目'
,return_career AS '回乡就业的意向职业'
,return_needs AS '返乡创业政策需求'
,addtime AS '创建时间'
,caijirenname AS '采集人'
FROM ps_zwjy AS A LEFT JOIN ps_city AS B ON A.job_province=CONVERT(NVARCHAR(50),B.id)
LEFT JOIN ps_city AS C ON A.job_city=CONVERT(NVARCHAR(50),C.id)
LEFT JOIN ps_city AS D ON A.job_county=CONVERT(NVARCHAR(50),D.id) where " + Literal1.Text.Trim().Replace("id","A.id");
DataTable Dt = DbHelperSQL.Query(Sql).Tables[0];
NPOIHelper.NewExportByWeb(Dt, "XXXXX.xlsx");
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
/// <summary>
/// NPOIHelper 的摘要说明
/// </summary>
public class NPOIHelper
{
#region 用于Web导出Excel文件
/// <summary>
/// 用于Web导出
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
public static void ExportByWeb(DataTable dt, string fileName)
{
string fileExpand = fileName.Substring(fileName.LastIndexOf(".") + 1, (fileName.Length - fileName.LastIndexOf(".") - 1));
string ctype = "";
int excelType = 0;
if ("xls" == fileExpand)
{
excelType = 1;
ctype = "application/vnd.ms-excel";
}
if ("xlsx" == fileExpand)
{
excelType = 2;
ctype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
HttpContext curContext = HttpContext.Current;
curContext.Response.ContentType = ctype;
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
curContext.Response.BinaryWrite(ExportEasy(dt, fileName, excelType).GetBuffer());
curContext.Response.End();
}
/// <summary>
/// 用户Web导出（无视浏览器，保持文件名不乱码）
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
public static void NewExportByWeb(DataTable dt, string fileName)
{
string fileExpand = fileName.Substring(fileName.LastIndexOf(".") + 1, (fileName.Length - fileName.LastIndexOf(".") - 1));
string ctype = "";
int excelType = 0;
if ("xls" == fileExpand)
{
excelType = 1;
ctype = "application/vnd.ms-excel";
}
if ("xlsx" == fileExpand)
{
excelType = 2;
ctype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
HttpContext curContext = HttpContext.Current;
MemoryStream ms = ExportEasy(dt, fileName, excelType);
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + string.Format("{0:yyyyMMdd}", DateTime.Now) + fileName);
curContext.Response.AddHeader("Content-Length", ms.Length.ToString());
curContext.Response.AddHeader("Content-Transfer-Encoding", "binary");
curContext.Response.ContentType = ctype;
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.BinaryWrite(ms.ToArray());
}
/// <summary>
/// 读取DataTable数据到Excel文件
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="fileName">文件名</param>
/// <returns></returns>
public static MemoryStream ExportEasy(DataTable dt, string fileName, int ExcelType=2)
{
IWorkbook workbook = null;
if (1 == ExcelType)
{
workbook = new HSSFWorkbook();
}
if (2 == ExcelType)
{
workbook = new XSSFWorkbook();
}
ISheet sheet = workbook.CreateSheet("Sheet1");
//填充表头
IRow headRow = sheet.CreateRow(0);
foreach (DataColumn column in dt.Columns)
{
headRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
IRow dataRow = null;
int rowIndex = 1;
int tempInt = 0;
double tempDouble = 0.0;
#region 设置单元格日期格式
DateTime tempDatetime;
ICellStyle style = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy-MM-dd");
ICell cell = null;
#endregion
string tempData = string.Empty;
foreach (DataRow row in dt.Rows)
{
//填充内容
dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
tempData = row[column].ToString();
//if (int.TryParse(tempData, out tempInt)) {
// dataRow.CreateCell(column.Ordinal).SetCellValue(tempInt);
// }
//else if (double.TryParse(tempData, out tempDouble)) {
// dataRow.CreateCell(column.Ordinal).SetCellValue(tempDouble);
// }
//else
if (DateTime.TryParse(tempData, out tempDatetime)) {
cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(tempDatetime);
cell.CellStyle = style;
}
else {
dataRow.CreateCell(column.Ordinal).SetCellValue(tempData);
}
}
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
workbook = null;
return ms;
}
/// <summary>
/// 读取DataTable数据到Excel文件
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="fileName">文件名</param>
/// <returns></returns>
public static void ExportEasy2(DataTable dt, string fileName, int ExcelType=2)
{
IWorkbook workbook = null;
if (1 == ExcelType)
{
workbook = new HSSFWorkbook();
}
if (2 == ExcelType)
{
workbook = new XSSFWorkbook();
}
ISheet sheet = workbook.CreateSheet("Sheet1");
//填充表头
IRow headRow = sheet.CreateRow(0);
foreach (DataColumn column in dt.Columns)
{
headRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
IRow dataRow = null;
int rowIndex = 1;
int tempInt = 0;
double tempDouble = 0.0;
#region 设置单元格日期格式
DateTime tempDatetime;
ICellStyle style = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy-MM-dd hh:mm:ss");
ICell cell = null;
#endregion
string tempData = string.Empty;
foreach (DataRow row in dt.Rows)
{
//填充内容
dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
tempData = row[column].ToString();
if (int.TryParse(tempData, out tempInt))
{
dataRow.CreateCell(column.Ordinal).SetCellValue(tempInt);
}
else if (double.TryParse(tempData, out tempDouble))
{
dataRow.CreateCell(column.Ordinal).SetCellValue(tempDouble);
}
else if (DateTime.TryParse(tempData, out tempDatetime))
{
cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(tempDatetime);
cell.CellStyle = style;
}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(tempData);
}
}
rowIndex++;
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
#endregion
/// <summary>
/// 读取Excel文件数据到DataTable
/// </summary>
/// <param name="strFileName">Excel文档路径</param>
/// <param name="sheetNum">Excel文档编号</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string strFileName, int sheetNum, ref DataTable datatable)
{
IWorkbook wb = null;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
wb = WorkbookFactory.Create(file);
}
try
{
if (wb != null)
{
//获取第零个工作薄
ISheet sheet = wb.GetSheetAt(sheetNum);
//根据首行 获取列数
int cellCount = sheet.GetRow(0).LastCellNum;
//定义临时变量 tempRow 工作薄行对象
IRow tempRow = null;
//定义临时变量 tempCell 单元格对象
ICell tempCell = null;
//定义临时变量 dataRow DataTable行对象
DataRow dataRow = null;
//除首行外，遍历工作薄中的每一行
for (int rowIndex = (sheet.FirstRowNum + 1); rowIndex <= sheet.LastRowNum; rowIndex++)
{
//获取工作薄行对象
tempRow = sheet.GetRow(rowIndex);
if (tempRow != null)
{
//初始化DataTable行对象
dataRow = datatable.NewRow();
//遍历工作薄行中的每一列
for (int cellIndex = 0; cellIndex < cellCount; cellIndex++)
{
//获取单元格对象
tempCell = tempRow.GetCell(cellIndex);
if (tempCell != null)
{
//给DataTable每行每列赋值
dataRow[cellIndex] = tempCell.ToString().Trim();
}
}
//DataTable加载行
datatable.Rows.Add(dataRow);
}
}
}
}
catch (Exception e)
{
}
return datatable;
}
public static DataTable ImportExcel(string filePath)
{
DataTable dt = new DataTable();
using (FileStream fsRead = System.IO.File.OpenRead(filePath))
{
IWorkbook wk = null;
//获取后缀名
string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
//判断是否是excel文件
if (extension == ".xlsx" || extension == ".xls")
{
//判断excel的版本
if (extension == ".xlsx")
{
wk = new XSSFWorkbook(fsRead);
}
else
{
wk = new HSSFWorkbook(fsRead);
}
//获取第一个sheet
ISheet sheet = wk.GetSheetAt(0);
//获取第一行
IRow headrow = sheet.GetRow(0);
//创建列
for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
{
DataColumn datacolum = new DataColumn(headrow.GetCell(i).StringCellValue);
//DataColumn datacolum = new DataColumn("F" + (i + 1));
dt.Columns.Add(datacolum);
}
//读取每行,从第二行起
for (int r = 1; r <= sheet.LastRowNum; r++)
{
bool result = false;
DataRow dr = dt.NewRow();
//获取当前行
IRow row = sheet.GetRow(r);
//读取每列
for (int j = 0; j < headrow.Cells.Count; j++)
{
ICell cell = row.GetCell(j); //一个单元格
dr[j] = GetCellValue(cell); //获取单元格的值
//全为空则不取
if (dr[j].ToString() != "")
{
result = true;
}
}
if (result == true)
{
dt.Rows.Add(dr); //把每行追加到DataTable
}
}
}
}
return dt;
}
//对单元格进行判断取值
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
return string.Empty;
case CellType.Boolean: //bool类型
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric: //数字类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
return cell.DateCellValue.ToString();
}
else //其它数字
{
return cell.NumericCellValue.ToString();
}
case CellType.Unknown: //无法识别类型
default: //默认类型
return cell.ToString();//
case CellType.String: //string 类型
return cell.StringCellValue;
case CellType.Formula: //带公式类型
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
}
public class NpoiMemoryStream : MemoryStream
{
public NpoiMemoryStream()
{
AllowClose = true;
}
public bool AllowClose { get; set; }
public override void Close()
{
if (AllowClose)
base.Close();
}
}
