C#用Aspose.Cells导出xls

本文主要是介绍C#用Aspose.Cells导出xls，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

 导出代码：

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;
using WuZiFenGongSiInfomation.Common;namespace WuZiFenGongSiInfomation.Export
{/// <summary>/// 导出excel/// </summary>/// 2019-11-5 15:56:05   添加public class ExportExcelHelpter{/// <summary>/// 导出excel/// </summary>/// <param name="data">数据</param>/// <param name="stream">导出流</param>public static void ExportExcel(List<List<string>> data, ref Stream stream){Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();Aspose.Cells.Worksheet sheet = wb.Worksheets[0];Aspose.Cells.Cells cells = sheet.Cells;Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];//style.Font.Name = "宋体";style.Font.Size = 12;cells.ApplyStyle(style, new StyleFlag() { All = true });int cols = data[0].Count;//标题样式Aspose.Cells.Style styleTitle = wb.Styles[wb.Styles.Add()];styleTitle.Font.IsBold = true;styleTitle.Font.Size = 12;Range range = cells.CreateRange(0, 0, 1, cols);range.ApplyStyle(styleTitle, new StyleFlag() { All = true });object[,] dataArr2 = new object[data.Count, cols];for (int n = 0; n < data.Count; n++){var rowLine = data[n];for (int j = 0; j < rowLine.Count; j++){dataArr2[n, j] = rowLine[j];}}cells.ImportTwoDimensionArray(dataArr2, 0, 0);//for (int i = 0; i < data.Count; i++)//{//    var item = data[i];//    for (int n = 0; n < item.Count; n++)//    {//        var valueCell = item[n];//        cells[i, n].PutValue(valueCell);//        if (i == 0)//        {//            cells[i, n].SetStyle(styleTitle);//        }//        else {//            cells[i, n].SetStyle(style);//        }//    }//}//自适应宽sheet.AutoFitColumns();//自适应行高sheet.AutoFitRows();string fileName = Guid.NewGuid().ToString("N") + ".xls";string filePath = AppDomain.CurrentDomain.BaseDirectory + fileName;//2020-8-5 09:53:58 添加string fileFolderDeire = Path.GetDirectoryName(filePath);//目录信息if (!System.IO.Directory.Exists(fileFolderDeire)){System.IO.Directory.CreateDirectory(fileFolderDeire);}//保存文件到本地wb.Save(filePath);stream.Seek(0, SeekOrigin.Begin);stream = new FileStream(filePath, FileMode.Open);Task.Run(() =>{//删除生成的文件System.Threading.Thread.Sleep(1000);try{File.Delete(filePath);}catch (Exception) { }});保存文件到本地//FileStream fileStream = new FileStream(filePath, FileMode.Create);//byte[] buff = new byte[stream.Length];stream.Write(buff, 0, (int)stream.Length);//stream.Read(buff,0, (int)stream.Length);//fileStream.Write(buff, 0, (int)stream.Length);//fileStream.Close();//fileStream.Dispose();}/// <summary>/// 导出excel文件，返回生成的web url地址/// </summary>/// <param name="data">导出的数据</param>/// <param name="fileName">生成的文件名称包含后缀</param>/// <returns></returns>public static string ExportExcelFile(List<List<string>> data, string fileName){Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();Aspose.Cells.Worksheet sheet = wb.Worksheets[0];Aspose.Cells.Cells cells = sheet.Cells;Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];//style.Font.Name = "宋体";style.Font.Size = 11;cells.ApplyStyle(style, new StyleFlag() { All = true });int cols = data[0].Count;//标题样式Aspose.Cells.Style styleTitle = wb.Styles[wb.Styles.Add()];styleTitle.Font.IsBold = true;styleTitle.Font.Size = 11;Range range = cells.CreateRange(0, 0, 1, cols);range.ApplyStyle(styleTitle, new StyleFlag() { All = true });object[,] dataArr2 = new object[data.Count, cols];for (int n = 0; n < data.Count; n++){var rowLine = data[n];for (int j = 0; j < rowLine.Count; j++){dataArr2[n, j] = rowLine[j];}}cells.ImportTwoDimensionArray(dataArr2, 0, 0);//自适应宽//sheet.AutoFitColumns();//2020-7-10 14:22:50 注释//自适应行高sheet.AutoFitRows();//string fileName = Guid.NewGuid().ToString("N") + ".xls";string filePath = AppDomain.CurrentDomain.BaseDirectory + Models.Veiw.CommonData.ExportFilefolder + fileName;//2020-8-5 09:53:58 添加string fileFolderDeire =   Path.GetDirectoryName(filePath);//目录信息if (!System.IO.Directory.Exists(fileFolderDeire)) {               System.IO.Directory.CreateDirectory(fileFolderDeire);}//保存文件到本地wb.Save(filePath);string url = Models.Veiw.CommonData.ExportFilefolder.Replace(@"\", @"/") + fileName;//记录生成的文件到缓存，用于删除const string cacheKey = "XLS_EXPORT_TO_DEL";List<string> filesExportList = MemoryCacheProvider.GetCacheItem<List<string>>(cacheKey);if (filesExportList != null){//var item = (filePath, DateTime.UtcNow);filesExportList.Add(filePath);MemoryCacheProvider.Set(cacheKey, filesExportList, DateTime.UtcNow.AddMinutes(240));}else{filesExportList = new List<string>();filesExportList.Add(filePath);MemoryCacheProvider.Set(cacheKey, filesExportList, DateTime.UtcNow.AddMinutes(240));}//删除文件Task.Run(() =>{System.Threading.Thread.Sleep(300000);ac(filesExportList);});return url;}/// <summary>/// 导出excel文件，返回生成的web url地址/// </summary>/// <param name="data">导出的数据，List<string>是一行的数据</param>/// <param name="fileName">生成的文件名，比如test.xls</param>/// <param name="columnsWidthSet">设置列宽,TKey是列下标，第一列为0；TValue是列宽度</param>/// <returns></returns>public static string ExportExcelFile(List<List<string>> data, string fileName, SortedList<int, double> columnsWidthSet = null){Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();Aspose.Cells.Worksheet sheet = wb.Worksheets[0];Aspose.Cells.Cells cells = sheet.Cells;Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];//style.Font.Name = "宋体";style.Font.Size = 11;cells.ApplyStyle(style, new StyleFlag() { All = true });int cols = data[0].Count;//标题样式Aspose.Cells.Style styleTitle = wb.Styles[wb.Styles.Add()];styleTitle.Font.IsBold = true;styleTitle.Font.Size = 11;Range range = cells.CreateRange(0, 0, 1, cols);range.ApplyStyle(styleTitle, new StyleFlag() { All = true });object[,] dataArr2 = new object[data.Count, cols];for (int n = 0; n < data.Count; n++){var rowLine = data[n];for (int j = 0; j < rowLine.Count; j++){dataArr2[n, j] = rowLine[j];}}cells.ImportTwoDimensionArray(dataArr2, 0, 0);//cells.SetColumnWidth(0, 31.29);//设置列宽，标题（第一列）//cells.SetColumnWidth(1, 9);//设置列宽，标题//cells.SetColumnWidth(2, 20.29);//设置列宽，标题标题//cells.SetColumnWidth(3, 30);//设置列宽，作者//cells.SetColumnWidth(4, 33);//设置列宽，栏目//cells.SetColumnWidth(6, 33);//设置列宽，意见//cells.SetColumnWidth(8, 23);//设置列宽，审核单位//cells.SetColumnWidth(9, 21.5);//设置列宽，审核时间if (columnsWidthSet!=null && columnsWidthSet.Count>0){foreach (var columsWidth in columnsWidthSet){cells.SetColumnWidth(columsWidth.Key, columsWidth.Value);//设置列宽}}//自适应宽            //sheet.AutoFitColumns();//2020-7-10 14:22:50 注释//自适应行高sheet.AutoFitRows();//string fileName = Guid.NewGuid().ToString("N") + ".xls";string filePath = AppDomain.CurrentDomain.BaseDirectory + Models.Veiw.CommonData.ExportFilefolder + fileName;//2020-8-5 09:53:58 添加string fileFolderDeire = Path.GetDirectoryName(filePath);//目录信息if (!System.IO.Directory.Exists(fileFolderDeire)){System.IO.Directory.CreateDirectory(fileFolderDeire);}//保存文件到本地wb.Save(filePath);string url = Models.Veiw.CommonData.ExportFilefolder.Replace(@"\", @"/") + fileName;//记录生成的文件到缓存，用于删除const string cacheKey = "XLS_EXPORT_TO_DEL";List<string> filesExportList = MemoryCacheProvider.GetCacheItem<List<string>>(cacheKey);if (filesExportList != null){//var item = (filePath, DateTime.UtcNow);filesExportList.Add(filePath);MemoryCacheProvider.Set(cacheKey, filesExportList, DateTime.UtcNow.AddMinutes(240));}else{filesExportList = new List<string>();filesExportList.Add(filePath);MemoryCacheProvider.Set(cacheKey, filesExportList, DateTime.UtcNow.AddMinutes(240));}//删除文件Task.Run(() =>{System.Threading.Thread.Sleep(300000);ac(filesExportList);});return url;}/// <summary>/// 删除已经过期的文件/// </summary>private readonly static Action<List<string>> ac = (list) =>{if (list == null || list.Count == 0){return;}foreach (var delFile in list){if (File.Exists(delFile)){File.Delete(delFile);}}};}
}

方法调用参考：

 //驳回记录 到处excel  2020-7-10 11:39:59 添加[LoginFilter(Roles = "2700400")]public async Task<ActionResult> ExportRejectRecord(CheckHistoryWhere where, int pageIndex = 1, int pageSize = 500){await Task.Yield();var page = await articleCheckHistoryBll.PageMyHistoryAsync(where, pageIndex, 1);int total = page?.Total ?? 0;int exportRows = CommonData.AllowExportRows;int pageCount = (int)Math.Ceiling((decimal)page.Total / (decimal)exportRows);List<string> fileUrls = new List<string>();List<List<string>> list = new List<List<string>>();for (; pageIndex <= pageCount; pageIndex++){list.Clear();List<CheckHistoryView> data = null;var page2 = await articleCheckHistoryBll.PageMyHistoryAsync(where, pageIndex, exportRows);if (page2?.List != null){data = page2.List;}if (data == null){return Content("没有数据导出");}//标题var row = new List<string>();row.Add("标题");row.Add("报送人");row.Add("报送单位");row.Add("作者");row.Add("报送栏目");row.Add("进度");row.Add("意见");row.Add("审核人");row.Add("审核单位");row.Add("审核时间");list.Add(row);//数据for (int i = 0; i < data.Count; i++){var item = data[i];var rowData = new List<string>();rowData.Add(item.Title);rowData.Add(item.Reportor);rowData.Add(item.ReportorDepartment);rowData.Add(item.AuthorStr);rowData.Add(item.ChannerName);rowData.Add(item.StepTxt);rowData.Add(item.Remark);rowData.Add(item.CheckUserName);rowData.Add(item.CheckUserDepartmentName);rowData.Add(item.CheckTime.ToString("yyyy-MM-dd HH:mm:ss"));list.Add(rowData);}string fileName = string.Empty;if (total < exportRows){fileName = Guid.NewGuid().ToString("N") + ".xls";}else{fileName = Guid.NewGuid().ToString("N") + "_" + pageIndex + ".xls";}SortedList<int, double> columnsWidth = new SortedList<int, double>();columnsWidth.Add(0, 31.29);//设置列宽，标题（第一列）columnsWidth.Add(1, 9);//设置列宽，标题columnsWidth.Add(2, 20.29);//设置列宽，标题标题columnsWidth.Add(3, 30);//设置列宽，作者columnsWidth.Add(4, 33);//设置列宽，栏目columnsWidth.Add(6, 33);//设置列宽，意见columnsWidth.Add(8, 23);//设置列宽，审核单位columnsWidth.Add(9, 21.5);//设置列宽，审核时间string url = ExportExcelHelpter.ExportExcelFile(list,"驳回记录"+ fileName,columnsWidth);fileUrls.Add(url);}return Json(new Result(fileUrls), JsonRequestBehavior.AllowGet);}

 

 

 

这篇关于C#用Aspose.Cells导出xls的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---