1/excel帮助类（使用NPOI插件）
public class ExcelHelper
{
public static string Version
{
get { return "0.1"; }
}
readonly static int EXCEL03_MaxRow = 65535;
public static DataTable ToDataTable<T>(IEnumerable<T> collection, string lang = null)
{
var props = typeof(T).GetProperties();
var dt = new DataTable();
if (lang != null && lang.ToLower() == "zh-cn")
{
PropertyInfo[] peroperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo property in peroperties)
{
object[] objs = property.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objs.Length > 0)
{
dt.Columns.Add(((DescriptionAttribute)objs[0]).Description);
}
else
{
dt.Columns.Add(new DataColumn(property.Name, property.PropertyType));
}
}
}
else
{
dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
}
if (collection.Count() > 0)
{
for (int i = 0; i < collection.Count(); i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in props)
{
object obj = pi.GetValue(collection.ElementAt(i), null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
dt.LoadDataRow(array, true);
}
}
return dt;
}
public static FileStream SaveFile<T>(string path,IEnumerable<T> collection,string tableName,string lang)
{
try
{
IWorkbook book = new HSSFWorkbook();
var table = ToDataTable<T>(collection, lang);
table.TableName = tableName;
FillWorkBookData(book, table);
FileStream file = new FileStream(path, FileMode.Create);
book.Write(file);
file.Close();
file.Dispose();
return new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
}
catch (Exception ex)
{
return null;
}
}
public static FileStream SaveFile(string path, DataSet set)
{
try
{
IWorkbook book = new HSSFWorkbook();
for(int i = 0; i < set.Tables.Count;i++)
{
FillWorkBookData(book, set.Tables[i]);
}
FileStream file = new FileStream(path, FileMode.Create);
book.Write(file);
file.Close();
file.Dispose();
return new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
}
catch (Exception ex)
{
return null;
}
}
public static IWorkbook FillWorkBookDataForPage(IWorkbook book,DataTable dt)
{
book = book ?? new HSSFWorkbook();
if (dt.Rows.Count < EXCEL03_MaxRow)
FillWorkBookData(book,dt, 0, dt.Rows.Count - 1);
else
{
int page = dt.Rows.Count / EXCEL03_MaxRow;
for (int i = 0; i < page; i++)
{
int start = i * EXCEL03_MaxRow;
int end = (i * EXCEL03_MaxRow) + EXCEL03_MaxRow - 1;
dt.TableName = $"{dt.TableName}-i";
FillWorkBookData(book, dt, start, end);
}
int lastPageItemCount = dt.Rows.Count % EXCEL03_MaxRow;
dt.TableName = $"{dt.TableName}-{page}";
FillWorkBookData(book,dt, dt.Rows.Count - lastPageItemCount, lastPageItemCount);
}
return book;
}
public static IWorkbook FillWorkBookData(IWorkbook book, DataTable dt, ICellStyle style, int startRow = 0, int endRow = -1)
{
book = book ?? new HSSFWorkbook();
ISheet sheet = book.CreateSheet(dt.TableName);
ICellStyle cellStyle = book.CreateCellStyle();
IFont font = book.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = (short)FontBoldWeight.Bold;
cellStyle.BorderTop = cellStyle.BorderRight = cellStyle.BorderBottom = cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.SetFont(font);
IRow header = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = header.CreateCell(i);
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
cell.CellStyle = cellStyle;
}
int rowIndex = 1;
if (endRow < 0) endRow = dt.Rows.Count-1;
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
IRow excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
var col = excelRow.CreateCell(j);
col.SetCellValue(dtRow[j].ToString());
if (style != null)
{
col.CellStyle = style;
}
}
}
return book;
}
/// <summary>
/// 添加数据表格默认样式
/// </summary>
/// <param name="book"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static IWorkbook FillWorkBookData(IWorkbook book,DataTable dt, int startRow = 0, int endRow = -1)
{
book = book ?? new HSSFWorkbook();
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.BorderTop = cellStyle.BorderRight = cellStyle.BorderBottom = cellStyle.BorderLeft = BorderStyle.Thin;
return FillWorkBookData(book, dt, cellStyle, startRow, endRow);
}
}
2/API
[HttpPost, Route("api/Template/GetColumnFile")]
public HttpResponseMessage GetColumnFile([FromBody]GetColumnRequest req)
{
HttpResponseMessage result;
try
{
var columns = service.GetColumn(req.Object, req.TableName) ?? new List<VColumn>();
var tables = service.GetTable(req.Object) ?? new List<VTable>();
DataSet set = new DataSet();
var coltable = ExcelHelper.ToDataTable(columns, "zh-cn");
coltable.TableName = $"{req.TableName}表信息";
set.Tables.Add(coltable);
var tabTable = ExcelHelper.ToDataTable(tables);
tabTable.TableName = $"{req.Object.Database}数据库信息";
set.Tables.Add(tabTable);
string basePath = System.Web.Hosting.HostingEnvironment.MapPath(@"~/") + "TransitionFile\\";
string fileName = Guid.NewGuid().ToString();
string path = $"{basePath}{fileName}.xls";
DirectoryHelper.CreateIfNotExists(basePath);
var stream = ExcelHelper.SaveFile(path, set);
result = new HttpResponseMessage(System.Net.HttpStatusCode.OK) { Content = new StreamContent(stream) };
result.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");
result.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment")
{
FileName = HttpUtility.UrlEncode("123.xls")
};
return result;
}
catch (Exception ex)
{
return new HttpResponseMessage(HttpStatusCode.NotFound);
}
}
3/web端
testClick(){
var request = {
tableName: this.columnNodeData.label,
object: this.columnNodeData.conn
};
this.$http
.post("/api/Template/GetColumnFile",pamas,{ 'responseType': 'blob'}) //请求方式 post get put delete等等
.then(res => {
var blob = new Blob([res.data], {type: 'application/vnd.openxmlformats-officedocument.wordprocessingml.document;charset=utf-8'}); //application/vnd.openxmlformats-officedocument.wordprocessingml.document这里表示doc类型
var contentDisposition = res.headers['content-disposition']; //从response的headers中获取filename, 后端response.setHeader("Content-disposition", "attachment; filename=xxxx.docx") 设置的文件名;
var patt = new RegExp("filename=([^;]+\\.[^\\.;]+);*");
var result = patt.exec(contentDisposition);
var filename = result[1];
var downloadElement = document.createElement('a');
var href = window.URL.createObjectURL(blob); //创建下载的链接
downloadElement.style.display = 'none';
downloadElement.href = href;
downloadElement.download =filename ; //下载后文件名
document.body.appendChild(downloadElement);
downloadElement.click(); //点击下载
document.body.removeChild(downloadElement); //下载完成移除元素
window.URL.revokeObjectURL(href); //释放掉blob对象
})
.catch(err => {
this.$message.error(err);
});
}
4/excel转换为html
//var workbook = ExcelToHtmlUtils.LoadXls(path);
//ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter();
////set output parameter
//excelToHtmlConverter.OutputColumnHeaders = false;
//excelToHtmlConverter.OutputHiddenColumns = true;
//excelToHtmlConverter.OutputHiddenRows = true;
//excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = false;
//excelToHtmlConverter.OutputRowNumbers = true;
//excelToHtmlConverter.UseDivsToSpan = true;
////process the excel file
//excelToHtmlConverter.ProcessWorkbook(workbook);
////output the html file
//excelToHtmlConverter.Document.Save(Path.ChangeExtension(path, "html"));
