using NPOI.HSSF.UserModel;
|
using NPOI.HSSF.Util;
|
using NPOI.SS.UserModel;
|
using NPOI.SS.Util;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.ComponentModel;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using System.Web;
|
|
namespace VueWebApi.Tools
|
{
|
public static class DownLoad
|
{
|
///<summary>
|
/// 下载
|
///</summary>
|
///<typeparam name="T"></typeparam>
|
///<param name="data"></param>
|
///<param name="map">所需要的列</param>
|
///<param name="filenName">文件名字</param>
|
///<returns></returns>
|
public static string Download<T>(this IList<T> data, Dictionary<string, string> map, string filenName)
|
{
|
var filename = filenName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
|
string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
|
var filepath = HttpContext.Current.Server.MapPath("/File/newxls/");
|
var path = filepath + filename;
|
|
if (!Directory.Exists(filepath))
|
{
|
Directory.CreateDirectory(filepath);
|
}
|
var properties = TypeDescriptor.GetProperties(typeof(T));
|
var dt = new DataTable();
|
var list = map.Values;
|
//显示表结构
|
foreach (var row in list)
|
{
|
dt.Columns.Add(row);
|
}
|
//取值
|
foreach (var item in data)
|
{
|
var i = 0;
|
var values = new object[dt.Columns.Count];
|
foreach (var content in map)
|
{
|
var property = properties[content.Key];
|
if (property != null)
|
{
|
var value = property.GetValue(item);
|
if (property.PropertyType == typeof(decimal))
|
{
|
if ((decimal)value == 0)
|
{
|
value = 0;
|
}
|
else
|
{
|
value = ((decimal)value).ToString("0.00");
|
}
|
}
|
values[i] = value;
|
if (Convert.ToString(content).Contains("率"))
|
{
|
values[i] = value + "%";
|
}
|
|
}
|
else
|
{
|
values[i] = DBNull.Value;
|
}
|
i++;
|
}
|
dt.Rows.Add(values);
|
}
|
var b = DALSkillCondition.ResultExcel(path, dt);
|
if (b)
|
{
|
var url = fileip + "/apis/File/newxls/" + filename;
|
return url;
|
}
|
var messge = filename + "下载失败";
|
return messge;
|
}
|
|
/// <summary>
|
/// Datas the set to excel.
|
/// </summary>
|
/// <param name="ds">The ds.</param>
|
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
|
public static string DataSetToExcel(DataSet ds, string filenName)
|
{
|
try
|
{
|
string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
|
string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
|
string filePath = HttpContext.Current.Server.MapPath("/File/newxls/");
|
Directory.CreateDirectory(filePath);
|
string Path = filePath + fileName;
|
|
FileStream fs = null;
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
for (int i = 0; i < ds.Tables.Count; i++)
|
{
|
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
|
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
|
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
|
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
|
|
int rowIndex = 0;
|
|
#region 新建表，填充表头，填充列头，样式
|
|
if (rowIndex == 0)
|
{
|
#region 列头及样式
|
|
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
|
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
|
headStyle.Alignment = HorizontalAlignment.Center;
|
|
XSSFFont font = (XSSFFont)workbook.CreateFont();
|
font.FontName = "微软雅黑";
|
font.FontHeightInPoints = 12; //字体大小
|
font.Boldweight = 700;//字体加粗
|
headStyle.SetFont(font);
|
//自定义表头
|
for (var j = 0; j < ds.Tables[i].Columns.Count; j++)
|
{
|
sheet.SetColumnWidth(j, 30 * 256);//
|
//sheet.AutoSizeColumn(j); //自适应宽度
|
headerRow.CreateCell(j).SetCellValue(ds.Tables[i].Columns[j].ColumnName);
|
headerRow.GetCell(j).CellStyle = headStyle;
|
}
|
|
#endregion
|
|
rowIndex = 1;
|
}
|
|
#endregion
|
ICellStyle cellstyle = workbook.CreateCellStyle();
|
cellstyle.VerticalAlignment = VerticalAlignment.Center;
|
cellstyle.Alignment = HorizontalAlignment.Center;
|
foreach (DataRow row in ds.Tables[i].Rows)
|
{
|
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
|
|
#region 填充内容
|
|
foreach (DataColumn column in ds.Tables[i].Columns)
|
{
|
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
|
string type = row[column].GetType().FullName.ToString();
|
newCell.SetCellValue(GetValue(row[column].ToString(), type));
|
newCell.CellStyle = cellstyle;
|
}
|
|
#endregion
|
|
rowIndex++;
|
}
|
}
|
|
using (fs = File.OpenWrite(Path))
|
{
|
workbook.Write(fs);
|
var url = fileip + "/File/newxls/" + fileName;
|
return url;
|
}
|
}
|
catch (Exception e)
|
{
|
return e.Message;
|
}
|
}
|
|
/// <summary>
|
/// Datas the set to excel.
|
/// </summary>
|
/// <param name="ds">The ds.</param>
|
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
|
public static string DataTableToExcel(DataTable ds, string filenName)
|
{
|
try
|
{
|
string fileName = filenName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
|
string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
|
string filePath = HttpContext.Current.Server.MapPath("/File/newxls/");
|
Directory.CreateDirectory(filePath);
|
string Path = filePath + fileName;
|
|
FileStream fs = null;
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
|
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.TableName);
|
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
|
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
|
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
|
|
int rowIndex = 0;
|
|
#region 新建表，填充表头，填充列头，样式
|
|
if (rowIndex == 0)
|
{
|
#region 列头及样式
|
|
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
|
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
|
headStyle.Alignment = HorizontalAlignment.Center;
|
|
XSSFFont font = (XSSFFont)workbook.CreateFont();
|
font.FontName = "微软雅黑";
|
font.FontHeightInPoints = 12; //字体大小
|
font.Boldweight = 700;//字体加粗
|
headStyle.SetFont(font);
|
//自定义表头
|
for (var j = 0; j < ds.Columns.Count; j++)
|
{
|
sheet.SetColumnWidth(j, 30 * 256);//
|
//sheet.AutoSizeColumn(j); //自适应宽度
|
headerRow.CreateCell(j).SetCellValue(ds.Columns[j].ColumnName);
|
headerRow.GetCell(j).CellStyle = headStyle;
|
}
|
|
#endregion
|
|
rowIndex = 1;
|
}
|
|
#endregion
|
ICellStyle cellstyle = workbook.CreateCellStyle();
|
cellstyle.VerticalAlignment = VerticalAlignment.Center;
|
cellstyle.Alignment = HorizontalAlignment.Center;
|
foreach (DataRow row in ds.Rows)
|
{
|
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
|
|
#region 填充内容
|
|
foreach (DataColumn column in ds.Columns)
|
{
|
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
|
string type = row[column].GetType().FullName.ToString();
|
newCell.SetCellValue(GetValue(row[column].ToString(), type));
|
newCell.CellStyle = cellstyle;
|
}
|
|
#endregion
|
|
rowIndex++;
|
}
|
|
|
using (fs = File.OpenWrite(Path))
|
{
|
workbook.Write(fs);
|
var url = fileip + "/File/newxls/" + fileName;
|
return url;
|
}
|
}
|
catch (Exception e)
|
{
|
return e.Message;
|
}
|
}
|
|
/// <summary>
|
/// Gets the value.
|
/// </summary>
|
/// <param name="cellValue">The cell value.</param>
|
/// <param name="type">The type.</param>
|
/// <returns>System.String.</returns>
|
private static string GetValue(string cellValue, string type)
|
{
|
object value = string.Empty;
|
switch (type)
|
{
|
case "System.String"://字符串类型
|
value = cellValue;
|
break;
|
case "System.DateTime"://日期类型
|
System.DateTime dateV;
|
System.DateTime.TryParse(cellValue, out dateV);
|
value = dateV;
|
break;
|
case "System.Boolean"://布尔型
|
bool boolV = false;
|
bool.TryParse(cellValue, out boolV);
|
value = boolV;
|
break;
|
case "System.Int16"://整型
|
case "System.Int32":
|
case "System.Int64":
|
case "System.Byte":
|
int intV = 0;
|
int.TryParse(cellValue, out intV);
|
value = intV;
|
break;
|
case "System.Decimal"://浮点型
|
case "System.Double":
|
double doubV = 0;
|
double.TryParse(cellValue, out doubV);
|
value = doubV;
|
break;
|
case "System.DBNull"://空值处理
|
value = string.Empty;
|
break;
|
default:
|
value = string.Empty;
|
break;
|
}
|
return value.ToString();
|
}
|
|
|
/// <summary>
|
/// Datatable生成Excel表格并返回路径
|
/// </summary>
|
/// <param name="m_DataTable">Datatable</param>
|
/// <param name="s_FileName">文件名</param>
|
/// <returns></returns>
|
public static string DataToExcel(System.Data.DataTable m_DataTable, string filenName)
|
{
|
var filename = filenName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
|
string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
|
var filepath = HttpContext.Current.Server.MapPath("/File/newxls/");
|
var path = filepath + filename;
|
|
//string FileName = AppDomain.CurrentDomain.BaseDirectory + ("/Upload/Excel/") + filenName + ".xls"; //文件存放路径
|
if (System.IO.File.Exists(path)) //存在则删除
|
{
|
System.IO.File.Delete(path);
|
}
|
System.IO.FileStream objFileStream;
|
System.IO.StreamWriter objStreamWriter;
|
string strLine = "";
|
objFileStream = new System.IO.FileStream(path, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
|
objStreamWriter = new System.IO.StreamWriter(objFileStream, Encoding.Unicode);
|
for (int i = 0; i < m_DataTable.Columns.Count; i++)
|
{
|
|
strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题
|
}
|
objStreamWriter.WriteLine(strLine);
|
strLine = "";
|
for (int i = 0; i < m_DataTable.Rows.Count; i++)
|
{
|
for (int j = 0; j < m_DataTable.Columns.Count; j++)
|
{
|
if (m_DataTable.Rows[i].ItemArray[j] == null)
|
strLine = strLine + " " + Convert.ToChar(9); //写内容
|
else
|
{
|
string rowstr = "";
|
rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
|
if (rowstr.IndexOf("\r\n") > 0)
|
rowstr = rowstr.Replace("\r\n", " ");
|
if (rowstr.IndexOf("\t") > 0)
|
rowstr = rowstr.Replace("\t", " ");
|
strLine = strLine + rowstr + Convert.ToChar(9);
|
}
|
}
|
objStreamWriter.WriteLine(strLine);
|
strLine = "";
|
}
|
objStreamWriter.Close();
|
objFileStream.Close();
|
var url = fileip + "/File/newxls/" + filename;
|
return url;
|
}
|
|
|
|
/// <summary>
|
/// 工资报表Excel导出数据合并
|
/// </summary>
|
/// <param name="path"></param>
|
/// <param name="table"></param>
|
/// <param name="treeIndex"></param>
|
public static void DataTree(DataTable table, string filenName, int treeIndex, out string pathstring) // /apis/File/newxls/工资产量报表20200507114638.xls
|
{
|
var filename = filenName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
|
string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
|
var filepath = HttpContext.Current.Server.MapPath("/File/newxls/");
|
var path = filepath + filename;
|
using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
|
{
|
IWorkbook workBook = new HSSFWorkbook();
|
//现在使用的仍然是生成Excel2003的Excel文件，由于03对行数（65535）和列数（255）有限制，所以当数据超出范围后难免出错
|
//ArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') ...
|
if (Path.GetExtension(path).Equals(".xlsx", System.StringComparison.OrdinalIgnoreCase))
|
{
|
workBook = new XSSFWorkbook();
|
}
|
|
string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet1" : table.TableName;
|
ISheet sheet = workBook.CreateSheet(sheetName);
|
IRow row = null;
|
int colNum = 8; //合并的列数
|
//int colNum = table.Columns.Count;
|
//if (treeIndex < table.Columns.Count || treeIndex > 0)
|
//{
|
// colNum = treeIndex;
|
//}
|
|
ICellStyle cellCenterStyle = GetCenter(workBook);
|
//IFont font = workBook.CreateFont(); //创建一个字体样式对象
|
//font.FontName = "方正舒体"; //和excel里面的字体对应
|
//font.IsItalic = true; //斜体
|
//font.FontHeightInPoints = 16;//字体大小
|
//font.Boldweight = short.MaxValue;//字体加粗
|
//cellCenterStyle.SetFont(font); //将字体样式赋给样式对象
|
|
int beginNum = 1;//排除列头，从1开始
|
|
//处理表格列头
|
row = sheet.CreateRow(beginNum - 1);
|
for (int i = 0; i < table.Columns.Count; i++)
|
{
|
|
string strVal = table.Columns[i].ColumnName;
|
ICell cell = row.CreateCell(i);
|
cell.SetCellValue(strVal);
|
cell.CellStyle = cellCenterStyle;
|
row.Height = 450;
|
|
sheet.AutoSizeColumn(i);
|
|
List<int> lstColWidth = new List<int>();
|
//记录列长度
|
lstColWidth.Add(DataLength(strVal));
|
//设置列宽
|
int maxWidth = lstColWidth.Max() * 600;
|
sheet.SetColumnWidth(i, maxWidth);
|
}
|
|
//处理数据内容
|
for (int i = 0; i < table.Rows.Count; i++)
|
{
|
row = sheet.CreateRow(beginNum + i);
|
row.Height = 350;
|
for (int j = 0; j < table.Columns.Count; j++)
|
{
|
string strVal = table.Rows[i][j].ToString();
|
ICell currCell = row.CreateCell(j);
|
currCell.SetCellValue(strVal);
|
currCell.CellStyle = cellCenterStyle;
|
sheet.SetColumnWidth(j, 256 * 15);
|
}
|
}
|
|
for (int i = 0; i < colNum; i++) //数据条数
|
{
|
List<int> lstColWidth = new List<int>();
|
string currVal = string.Empty;
|
string nextVal = string.Empty;
|
for (int j = beginNum; j <= sheet.LastRowNum; j++)
|
{
|
currVal = sheet.GetRow(j).Cells[i].StringCellValue;
|
|
int mk = j;
|
if (!string.IsNullOrWhiteSpace(currVal))//排除 空值，空值不做合并处理
|
{
|
for (int k = j + 1; k <= sheet.LastRowNum; k++)
|
{
|
nextVal = sheet.GetRow(k).Cells[i].StringCellValue;
|
|
if (currVal != nextVal)
|
{
|
//因为k 累加所以导致当前值与下个值 不相同，所以记录 当前行数要 减去1
|
mk = k - 1;
|
break;
|
}
|
else if (k == sheet.LastRowNum) //边界值，处理最后一行，则提前Break 并记录当前 k
|
{
|
mk = k;
|
break;
|
}
|
}
|
}
|
|
if (mk != j)//排除 空值外，下个值的行数不等于当前行数，则需要合并
|
{
|
sheet.AddMergedRegion(new CellRangeAddress(j, mk, i, i));
|
|
//sheet.GetRow(j).GetCell(i).SetCellValue("");
|
}
|
//else
|
//{
|
// if (mk < sheet.LastRowNum)
|
// {
|
// if (sheet.GetRow(j).Cells[0].StringCellValue == sheet.GetRow(mk + 1).Cells[0].StringCellValue
|
// && sheet.GetRow(j).Cells[1].StringCellValue == sheet.GetRow(mk + 1).Cells[1].StringCellValue
|
// && sheet.GetRow(j).Cells[2].StringCellValue == sheet.GetRow(mk + 1).Cells[2].StringCellValue
|
// && sheet.GetRow(j).Cells[3].StringCellValue == sheet.GetRow(mk + 1).Cells[3].StringCellValue
|
// )
|
// {
|
// int mmk = mk + 1;
|
// sheet.AddMergedRegion(new CellRangeAddress(j, mmk, i, i));
|
// }
|
// }
|
//}
|
|
//if (i == 0) //如果是第一列，则 垂直水平居中
|
{
|
sheet.GetRow(j).Cells[i].CellStyle = cellCenterStyle;
|
}
|
//跳到执行下一个不同数据的行
|
j = mk;
|
|
//记录列长度
|
lstColWidth.Add(DataLength(currVal));
|
}
|
|
//设置列宽
|
//int maxWidth = lstColWidth.Max() * 600;
|
//sheet.SetColumnWidth(i, maxWidth);
|
}
|
//固定列、行 滚动时不变
|
//sheet.CreateFreezePane(3, 1, 3, 1);
|
|
//写入数据流
|
workBook.Write(fs);
|
var url = fileip + "/apis/File/newxls/" + filename;
|
pathstring = url;
|
}
|
}
|
|
|
private static ICellStyle GetCenter(IWorkbook workBook, short fontSize = 10)
|
{
|
ICellStyle cellStyle = workBook.CreateCellStyle();
|
IFont font = workBook.CreateFont();
|
font.FontName = "微软雅黑";
|
font.FontHeightInPoints = fontSize;
|
cellStyle.SetFont(font);
|
cellStyle.VerticalAlignment = VerticalAlignment.Center;
|
cellStyle.Alignment = HorizontalAlignment.Center;
|
return cellStyle;
|
}
|
|
|
/// <summary>
|
/// 获取字符串长度（中文按2个字节长度）
|
/// </summary>
|
/// <param name="stringWithEnglishAndChinese"></param>
|
/// <returns></returns>
|
private static int DataLength(string stringWithEnglishAndChinese)
|
{
|
int lng = 0;
|
for (int i = 0; i < stringWithEnglishAndChinese.Length; i++)
|
{
|
byte[] b = System.Text.Encoding.Default.GetBytes(stringWithEnglishAndChinese.Substring(i, 1));
|
if (b.Length > 1)
|
lng += 2;
|
else
|
lng += 1;
|
}
|
return lng;
|
}
|
|
|
}
|
}
|
