|
Server : Microsoft-IIS/10.0 System : Windows NT EBS-6136 10.0 build 17763 (Windows Server 2016) i586 User : jxgj ( 0) PHP Version : 7.0.33 Disable Function : passthru,exec,system,shell_exec,proc_open,popen,pcntl_exec,socket_bind,stream_socket_server Directory : D:/tmp_aspnet/root/2591dbd7/c1451959/ |
?#pragma checksum "D:\wwwroot\heiyu1153\wwwroot\App_Code\hy\GemBoxExcelLiteHelper.cs" "{ff1816ec-aa5e-4d10-87f7-6f4963833460}" "C17EDA75AC5312846F2E17007457600F65F52A0B"
#line 1 "D:\wwwroot\heiyu1153\wwwroot\App_Code\hy\GemBoxExcelLiteHelper.cs"
using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Web.Security;
using GemBox.ExcelLite;
namespace Net.Template.Common
{
/// <summary>
/// Excel 操作类
/// 选取GemBox.ExcelLite第三方控件
/// </summary>
public class GemBoxExcelLiteHelper
{
/// <summary>
/// 天生Excel
/// </summary>
/// <param name="path">绝对蹊径</param>
/// <param name="page"></param>
/// <param name="isDownload">是否提供下载,true是,false否</param>
/// <param name="isDelete">是否删除本地天生的Excel,true是,false否</param>
/// <param name="titles">Excel标题</param>
/// <param name="ds">数据</param>
public static void SaveExcel(string path, System.Web.UI.Page page, bool isDownload, bool isDelete, bool hb, IList<String> titles, DataTable ds)
{
try
{
//保留在本地
SaveToXls(path, titles, ds, hb);
if (isDownload)
{
//提供下载
UploadExcel(path, page, isDelete);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 提供下载
/// </summary>
/// <param name="path"></param>
/// <param name="page"></param>
/// <param name="isDelete"></param>
private static void UploadExcel(string path, System.Web.UI.Page page, bool isDelete)
{
System.IO.FileInfo file = new System.IO.FileInfo(path);
page.Response.Clear();
page.Response.Charset = "GB2312";
page.Response.ContentEncoding = System.Text.Encoding.UTF8;
// 增长头信息，为"文件下载/另存为"对话框指定默认文件名
page.Response.AddHeader("Content-Disposition", "attachment; filename=" + page.Server.UrlEncode(file.Name));
// 增长头信息，指定文件大幼，让浏览器可能显示下载进度
page.Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
page.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
page.Response.WriteFile(file.FullName);
page.Response.Flush();
if (isDelete)
{
System.IO.File.Delete(path);
}
// 终场页面的执行
page.Response.End();
}
//ExcelCell Excel单元格
//ExcelCellCollection Excel单元格集中
//ExcelColumn Excel列
//ExcelColumnCollection Excel列集中
//ExcelColumnRowBase Excel列行库
//ExcelComment Excel注解
//ExcelFillPattern Excel填充模式
//ExcelFont Excel字体
//ExcelLite Excel精简版
//ExcelPicture Excel图片
//ExcelPictureCollection Excel图片集
//ExcelPrintOptions Excel打印选项
//ExcelRow Excel行
//ExcelRowCollection Excel行集中
//ExcelRowColumnCellCollectionBase Excel行列单元格集中库
//ExcelRowColumnCollectionBase Excel行列集中库
//ExcelViewOptions Excel视图选项
//ExcelWorksheetCollection Excel工作表集中
private static void SaveToXls(string path, IList<String> titles, DataTable dt, bool hb)
{
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet sheet = excelFile.Worksheets.Add("Sheet1");
sheet.DefaultColumnWidth = Convert.ToInt32(220 / 0.028);//默认列宽220像素
if (titles != null && titles.Count > 0)
{
for (int i = 0; i < titles.Count; i++)
{
sheet.Cells[0, i].Value = titles[i];
sheet.Cells[0, i].Style.Font.Name = "宋体";
sheet.Cells[0, i].Style.Font.Weight = 600;
sheet.Cells[0, i].SetBorders(MultipleBorders.Outside, System.Drawing.Color.Aquamarine, LineStyle.Thin);
sheet.Cells[0, i].Style.FillPattern.SetPattern(FillPatternStyle.Solid, System.Drawing.Color.LightSeaGreen, System.Drawing.Color.LightSeaGreen);
}
}
IList<string> list = new List<string>();
for (int j = 0; j < dt.Columns.Count; j++)
{
int col1 = 0;
int col2 = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][j].ToString().Trim() == "")
{
col2 = i + 1;
}
else
{
if (col1 < col2 && col1 > 0)
{
list.Add(col1 + "_" + col2 + "_" + j);
}
col1 = i + 1;
}
sheet.Cells[i + 1, j].Style.Font.Name = "宋体";
sheet.Cells[i + 1, j].Value = dt.Rows[i][j].ToString();
sheet.Cells[i + 1, j].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
sheet.Cells[i + 1, j].SetBorders(MultipleBorders.Outside, System.Drawing.Color.Aquamarine, LineStyle.Thin);
sheet.Cells[i + 1, j].Style.FillPattern.SetPattern(FillPatternStyle.Solid, System.Drawing.Color.AliceBlue, System.Drawing.Color.AliceBlue);
}
if (col1 < col2 && col1 > 0)
{
list.Add(col1 + "_" + col2 + "_" + j);
}
}
if (hb)
{
for (int i = 0; i < list.Count; i++)
{
//归并单元格
int a = Convert.ToInt32(list[i].ToString().Split('_')[0]);
int b = Convert.ToInt32(list[i].ToString().Split('_')[1]);
int c = Convert.ToInt32(list[i].ToString().Split('_')[2]);
sheet.Cells.GetSubrangeAbsolute(a, c, b, c).Merged = true;
sheet.Cells.GetSubrangeAbsolute(a, c, b, c).SetBorders(MultipleBorders.Outside, System.Drawing.Color.Aquamarine, LineStyle.Thin);
}
}
excelFile.SaveXls(path);
}
}
}
#line default
#line hidden