导出 工具类
导出(一)
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/json";
RuiJie.PBA.BLL.Order_JobData bllJob = new RuiJie.PBA.BLL.Order_JobData();
string type = context.Request.QueryString["type"];
switch (type)
{
case "netSalesData":
DataTable dtNetSalesData = bllJob.getDownloadData("dbo.Download_Net_Sales").Tables[0];
//修改列名
dtNetSalesData.Columns["salesman_name"].ColumnName = "姓名";
dtNetSalesData.Columns["order_task"].ColumnName = "订单任务（万）";
dtNetSalesData.Columns["total_net_sales"].ColumnName = "净销售额（万）";
dtNetSalesData.Columns["net_sales_complete_rate"].ColumnName = "订单任务完成率";
dtNetSalesData.Columns["dept"].ColumnName = "一级部门";
dtNetSalesData.Columns["region"].ColumnName = "二级部门";
dtNetSalesData.Columns["area"].ColumnName = "三级部门";
dtNetSalesData.Columns["groups"].ColumnName = "四级部门";
dtNetSalesData.Columns["role"].ColumnName = "岗位/权限";
ToExcel(dtNetSalesData, "净销额数据下载", "净销额", context);
break;
}
}
/// <summary>
/// 下载导出Excel
/// </summary>
/// <param name="_dtSouse">数据源</param>
/// <param name="_strFileName">文件名</param>
/// <param name="_strHeaderText">表头</param>
/// <param name="context"></param>
public void ToExcel(DataTable _dtSouse, string _strFileName, string _strHeaderText, HttpContext context)
{
try
{
System.Diagnostics.Stopwatch timeWatch = System.Diagnostics.Stopwatch.StartNew(); //计时器
string strFileName = _strFileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls";
//在服务器上创建一个文件下载的存储文件夹
if (!Directory.Exists(HttpContext.Current.Server.MapPath("~/TempDownload/")))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/TempDownload/"));
}
string strFilePath = HttpContext.Current.Server.MapPath("~/TempDownload/") + strFileName;
DataTableToExcel(_dtSouse, _strHeaderText, strFilePath);
strFileName = "TempDownload/" + strFileName;
string strJson = "{\"type\":\"" + "1" + "\",\"time\":\"" + timeWatch.Elapsed + "\",\"path\":\"" + strFileName + "\"}";
context.Response.Write(strJson);
}
catch (Exception e)
{
string strJson = "{\"type\"：\"" + "0" + "\",\"error\":\"" + e.Message + "\"}";
context.Response.Write(strJson);
}
}
/// <summary>
/// datatable导出到Excel文件
/// </summary>
/// <param name="dtSource">源datatable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
public static void DataTableToExcel(DataTable dtSource, string strHeaderText, string strFilePath)
{
using (MemoryStream ms = DataTableToExcel(dtSource, strHeaderText))
{
using (FileStream fs = new FileStream(strFilePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
}
}
}
/// <summary>
/// datatable 导出Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源datatable</param>
/// <param name="strHeaderText">表头文本</param>
/// <returns></returns>
public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("工作表");
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "author";
//si.ApplicationName = "applicationName";
//si.LastAuthor = "saver";
//si.Comments = "comments";
si.Title = "订单数据下载";
si.Subject = "订单数据下载";
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
//设置列头背景色
NPOI.SS.UserModel.ICellStyle colorStyle = workbook.CreateCellStyle();
colorStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
colorStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index;
headerRow.GetCell(column.Ordinal).CellStyle = colorStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
//保存
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
//workbook.Dispose();
return ms;
}
}
导出(二)
/// <summary>
/// 导出Excel数据操作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExcel_Click(object sender, EventArgs e)
{
if (!CheckAuth("Special-Form", "m20160523"))
{
Jss.WindowAlert(this, "无权限导出Excel", false);
BindDate();
return;
}
string strWhere = GetStrWhere();
DSys_Comm bll = new DSys_Comm();
DataTable dt = new Dspecial20151204_user().GetList(GetStrWhere());
ExportXls(this.Page, "m20160523", dt);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="page"></param>
/// <param name="fileName"></param>
/// <param name="dt"></param>
public void ExportXls(Page page, string fileName, DataTable dt)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "m20160509";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "m20160509";
hssfworkbook.SummaryInformation = si;
HSSFSheet sheet;
HSSFRow rowTitle;
HSSFCell cellTitle;
decimal rowCount = dt.Rows.Count;
int SheetSize = 5000;
decimal sheetCount = Math.Round((rowCount + SheetSize / 2) / SheetSize, 0);
for (int s = 0; s < sheetCount; s++)
{
sheet = hssfworkbook.CreateSheet("Sheet" + s);
rowTitle = sheet.CreateRow(0);
cellTitle = rowTitle.CreateCell(0);
cellTitle.SetCellValue("录入时间");
cellTitle = rowTitle.CreateCell(1);
cellTitle.SetCellValue("姓名");
cellTitle = rowTitle.CreateCell(2);
cellTitle.SetCellValue("公司");
cellTitle = rowTitle.CreateCell(3);
cellTitle.SetCellValue("联系方式");
cellTitle = rowTitle.CreateCell(4);
cellTitle.SetCellValue("部门");
cellTitle = rowTitle.CreateCell(5);
cellTitle.SetCellValue("职位");
cellTitle = rowTitle.CreateCell(6);
cellTitle.SetCellValue("邮箱");
cellTitle = rowTitle.CreateCell(7);
cellTitle.SetCellValue("您是否参加周六自由交流活动");
for (int i = s * SheetSize; i < (s + 1) * SheetSize; i++)
{
if (i >= rowCount)
{
break;
}
HSSFRow row = sheet.CreateRow(i % SheetSize + 1);
HSSFCell cell = row.CreateCell(0);
cell.SetCellValue(((DateTime)dt.Rows[i]["addtime"]).ToString("yyyy-MM-dd HH:mm:ss"));
cell = row.CreateCell(1);
if (dt.Rows[i]["username"] == DBNull.Value)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i]["username"].ToString());
}
cell = row.CreateCell(2);
if (dt.Rows[i]["company"] == DBNull.Value)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i]["company"].ToString());
}
cell = row.CreateCell(3);
if (dt.Rows[i]["phone"] == DBNull.Value)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i]["phone"].ToString());
}
cell = row.CreateCell(4);
if (dt.Rows[i]["department"] == DBNull.Value)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i]["department"].ToString());
}
cell = row.CreateCell(5);
if (dt.Rows[i]["position"] == DBNull.Value)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i]["position"].ToString());
}
cell = row.CreateCell(6);
if (dt.Rows[i]["industry"] == DBNull.Value)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i]["industry"].ToString());
}
cell = row.CreateCell(7);
if (dt.Rows[i]["optionsRadios"] == DBNull.Value)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i]["optionsRadios"].ToString());
}
}
}
string filename = fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls";
page.Response.ContentType = "application/vnd.ms-excel";
page.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
page.Response.Clear();
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
page.Response.BinaryWrite(file.GetBuffer());
page.Response.End();
}
}
#region 数据操作类
/// <summary>
/// 数据操作类
/// </summary>
public class Dspecial20151204_user : UI_A_DALBaseClass
{
/// <summary>
/// 获得列表
/// </summary>
/// <param name="top"></param>
/// <param name="strWhere"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id, username, company, phone,department,position,industry,optionsRadios, addtime ");
strSql.Append(" from m20160509 ");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" where " + strWhere);
}
using (DataSet ds = SQLHelper.ExecuteDataset(strConn, CommandType.Text, strSql.ToString(), null))
{
if (ds != null)
{
return ds.Tables[0];
}
else
{
return null;
}
}
}
}
#endregion
