使用NOPI构成Excel 写入到输出流 基础代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NPOI.XSSF.UserModel; using System.IO; namespace ConsoleApplication4 { class Program { static void Main(string[] args) { // 1 创建好内容 //创建workbook工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); //在workbook工作簿下创建两个工作表 workbook.CreateSheet("SheetFirst"); workbook.CreateSheet("SheetSecond"); //获取工作表SheetFirst XSSFSheet worksheet = (XSSFSheet)workbook.GetSheet("SheetFirst"); //在工作表下面创建10行 for (int i = 0; i < 10; i++) { worksheet.CreateRow(i); } //获取10行中的首行 XSSFRow sheetrow = (XSSFRow)worksheet.GetRow(0); //申明一个长度10的单元格数组 XSSFCell[] sheetcell = new XSSFCell[10]; for (int i = 0; i < sheetcell.Length; i++) { //创建10个单元格 sheetcell[i] = (XSSFCell)sheetrow.CreateCell(i); //分别赋值 sheetcell[i].SetCellValue(i); } // 2 将内容写入到流 //写入流 using (FileStream fs = new FileStream("TestNOPI.xlsx", FileMode.Create)) {// 指定的路径 和 创建模式（如何打开或创建文件的常数。） // //FileStream 构造函数 (String, FileMode, FileAccess ) 读/写权限 //using (FileStream file = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read)) //FileFullPath 路径 //FileMode.Open 打开现有文件(项目中打开模板) //FileAccess.Read 对文件的读访问。 可从文件中读取数据。 与 Write 组合以进行读写访问。 workbook.Write(fs); //Write out this document to an Outputstream. 将此文档写入输出流。 //参数为 希望写入文件的 - 输出流 } Console.WriteLine("OK"); Console.ReadKey(); } } }
文件默认写入到了 ConsoleApplication1\ConsoleApplication4\bin\Debug 项目bin\Debug 下
这一步 初始化 FileStream 类的新实例
但文件大小为0KB
这一步 写入 有数据了
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NPOI.XSSF.UserModel; using System.IO; namespace ConsoleApplication4 { class Program { static void Main(string[] args) { // 1 创建好内容 //创建workbook工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); //在workbook工作簿下创建两个工作表 workbook.CreateSheet("SheetFirst"); workbook.CreateSheet("SheetSecond"); //获取工作表SheetFirst XSSFSheet worksheet = (XSSFSheet)workbook.GetSheet("SheetFirst"); //在工作表下面创建10行 for (int i = 0; i < 10; i++) { worksheet.CreateRow(i); } //获取10行中的首行 XSSFRow sheetrow = (XSSFRow)worksheet.GetRow(0); //申明一个长度10的单元格数组 XSSFCell[] sheetcell = new XSSFCell[10]; for (int i = 0; i < sheetcell.Length; i++) { //创建10个单元格 sheetcell[i] = (XSSFCell)sheetrow.CreateCell(i); //分别赋值 sheetcell[i].SetCellValue(i); } // 2 将内容写入到流 //写入流 using (FileStream fs = new FileStream("TestNOPI.xlsx", FileMode.Create)) // 这一步之后 创建好文件了 但为0KB (还未写入数据) {// 指定的路径 和 创建模式（如何打开或创建文件的常数。） // //FileStream 构造函数 (String, FileMode, FileAccess ) 读/写权限 //using (FileStream file = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read)) //FileFullPath 路径 //FileMode.Open 打开现有文件(项目中打开模板) //FileAccess.Read 对文件的读访问。 可从文件中读取数据。 与 Write 组合以进行读写访问。 workbook.Write(fs); //// 这一步之后 写入数据 才有数据 //Write out this document to an Outputstream. 将此文档写入输出流。 //参数为 希望写入文件的 - 输出流 } Console.WriteLine("OK"); Console.ReadKey(); // 没有模板 创建内容 -- 创建文件流(没数据) -- 往文件流写数据 （这里） // bs 项目中 有模板 打开并读取模板 -- 根据模板创建内容 -- 创建内存流 -- 根据内容往内存流写数据 -- 导出内存流 #region MyRegion // public FileResult GetRouteTotalExport2(string line, string startdate, string RouteName, string TotalStates, List<string> branchid) //{ // string[] ab = { "1" }; // SortByDocument sbd = new SortByDocument(); // IMongoQuery query = null; // List<IMongoQuery> list = new List<IMongoQuery>(); // if (!string.IsNullOrEmpty(startdate)) // { // list.Add(Query.GTE("JobDate", DateTime.Parse(startdate))); // list.Add(Query.LT("JobDate", DateTime.Parse(startdate).AddDays(1))); // } // if (!string.IsNullOrEmpty(RouteName)) // { // list.Add(Query.Matches("RouteName", RouteName)); // } // //if (branchid.Count > 0) // //{ // // list.Add(Query.Matches("RouteName", RouteName)); // //} // query = Query.And(list); // List<RouteTotal> task = Common.MongodbHelper.FindListBy<RouteTotal>(query, sbd, "table_RouteTotal"); // string templetpath = System.Configuration.ConfigurationSettings.AppSettings["TempletsPath"].ToString(); // string FileFullPath = Server.MapPath(templetpath) + "RouteTotal.xls"; // if (!System.IO.File.Exists(FileFullPath)) // { // return null; // } // HSSFWorkbook workbook = new HSSFWorkbook(); // using (FileStream file = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read)) // { // workbook = (HSSFWorkbook)WorkbookFactory.Create(file); // } // HSSFSheet sheet1 = (HSSFSheet)workbook.GetSheetAt(0); // int data4 = 4; // string forat = ""; // foreach (var item in task) // { // CreateRow(sheet1, data4); // CreateCell(sheet1, data4, 0); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(0).SetCellValue(data4 - 3); // CreateCell(sheet1, data4, 1); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(1).SetCellValue(item.JobDate.GetValueOrDefault().ToString("yyyy-MM-dd")); // CreateCell(sheet1, data4, 2); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(2).SetCellValue(item.Branch); // CreateCell(sheet1, data4, 3); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(3).SetCellValue(item.RouteName); // CreateCell(sheet1, data4, 4); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(4).SetCellValue(item.CrewLeader); // CreateCell(sheet1, data4, 5); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(5).SetCellValue(item.CrewDriver); // CreateCell(sheet1, data4, 6); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(6).SetCellValue(item.CrewMember); // CreateCell(sheet1, data4, 7); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(7).SetCellValue(item.FingerScanin != null ? item.FingerScanin.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 8); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(8).SetCellValue(item.FingerScanOut != null ? item.FingerScanOut.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 9); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(9).SetCellValue(item.Rosteredstart != null ? item.Rosteredstart.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 10); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(10).SetCellValue(item.Inbaytime != null ? item.Inbaytime.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 11); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(11).SetCellValue(item.Outbaytime != null ? item.Outbaytime.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 12); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(12).SetCellType(CellType.FORMULA); // forat = @"IF(K{0}="""","""",K{0}-J{0})"; // forat = string.Format(forat, (data4 + 1)); // //5="","",K5-J5)"; // ((HSSFRow)sheet1.GetRow(data4)).GetCell(12).SetCellFormula(forat); // CreateCell(sheet1, data4, 13); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(13).SetCellType(CellType.FORMULA); // forat = @"IF(L{0}="""","""",L{0}-K{0})"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(13).SetCellFormula(forat); // CreateCell(sheet1, data4, 14); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(14).SetCellValue(item.Trip1_Depart != null ? item.Trip1_Depart.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 15); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(15).SetCellValue(item.Trip1_Return != null ? item.Trip1_Return.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 16); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(16).SetCellValue(item.Trip2_Depart != null ? item.Trip2_Depart.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 17); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(17).SetCellValue(item.Trip2_Return != null ? item.Trip2_Return.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 18); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(18).SetCellValue(item.Trip3_Depart != null ? item.Trip3_Depart.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 19); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(19).SetCellValue(item.Trip3_Return != null ? item.Trip3_Return.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 20); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(20).SetCellValue(item.Trip4_Depart != null ? item.Trip4_Depart.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 21); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(21).SetCellValue(item.Trip4_Return != null ? item.Trip4_Return.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 22); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(22).SetCellValue(item.Trip5_Depart != null ? item.Trip5_Depart.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 23); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(23).SetCellValue(item.Trip5_Return != null ? item.Trip5_Return.GetValueOrDefault().ToString("hh:mm:ss") : ""); // CreateCell(sheet1, data4, 24); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(24).SetCellValue(item.Returnbaytime.GetValueOrDefault().ToString("hh:mm:ss")); // CreateCell(sheet1, data4, 25); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(25).SetCellValue(item.Runfinish.GetValueOrDefault().ToString("hh:mm:ss")); // CreateCell(sheet1, data4, 26); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(26).SetCellType(CellType.FORMULA); // forat = @"IFERROR(IF(Z{0}-J{0}=0,"""",(Z{0}-J{0})+(Z{0}<J{0})),"""")"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(26).SetCellFormula(forat); // // // CreateCell(sheet1, data4, 27); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(27).SetCellType(CellType.FORMULA); // forat = @"IF(L{0}="""","""",L{0}-J{0})"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(27).SetCellFormula(forat); // CreateCell(sheet1, data4, 28); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(28).SetCellType(CellType.FORMULA); // forat = @"IF(AA{0}="""","""",Z{0}-Y{0})"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(28).SetCellFormula(forat); // //60 // CreateCell(sheet1, data4, 29); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(29).SetCellValue("60"); // CreateCell(sheet1, data4, 30); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(30).SetCellValue(item.Crewno); // CreateCell(sheet1, data4, 31); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(31).SetCellValue(item.Jobs_completed); // CreateCell(sheet1, data4, 32); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(32).SetCellValue(item.Jobsnotcompleted); // CreateCell(sheet1, data4, 33); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(33).SetCellType(CellType.FORMULA); // forat = @"AF{0}+AG{0}"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(33).SetCellFormula(forat); // CreateCell(sheet1, data4, 34); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(34).SetCellType(CellType.FORMULA); // forat = @"IF(AB{0}="""","""",HOUR(AB{0})+(MINUTE(AB{0})/60)-(AE{0}/60))"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(34).SetCellFormula(forat); // CreateCell(sheet1, data4, 35); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(35).SetCellType(CellType.FORMULA); // forat = @"IF(AB{0}="""","""",HOUR(AB{0})+(MINUTE(AB{0})/60))"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(35).SetCellFormula(forat); // CreateCell(sheet1, data4, 36); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(36).SetCellType(CellType.FORMULA); // forat = @"IFERROR(AI{0}/AH{0},"""")"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(36).SetCellFormula(forat); // CreateCell(sheet1, data4, 37); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(37).SetCellType(CellType.FORMULA); // forat = @"IFERROR(AH{0}/AI{0},"""")"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(37).SetCellFormula(forat); // CreateCell(sheet1, data4, 38); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(38).SetCellValue(item.TruckNumber); // CreateCell(sheet1, data4, 39); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(39).SetCellValue(item.StartKms); // CreateCell(sheet1, data4, 40); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(40).SetCellValue(item.FinishKms); // CreateCell(sheet1, data4, 41); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(41).SetCellType(CellType.FORMULA); // forat = @"IF(AO{0}="""","""",AO{0}-AN{0})"; // forat = string.Format(forat, (data4 + 1)); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(41).SetCellFormula(forat); // CreateCell(sheet1, data4, 42); // ((HSSFRow)sheet1.GetRow(data4)).GetCell(39).SetCellValue(item.Comments); // data4++; // } // System.IO.MemoryStream ms = new System.IO.MemoryStream(); // workbook.Write(ms); // ms.Seek(0, SeekOrigin.Begin); // string dateTime = DateTime.Today.ToString("ddMMyyyy");//yyMMddHHmmssfff // string fileName = "Route Total " + dateTime + ".xls"; // return File(ms, "application/vnd.ms-excel", fileName); //} #endregion // bs 项目中 没模板 创建内容 -- 创建内存流 -- 根据内容往内存流写数据 -- 导出内存流 #region MyRegion // public static MemoryStream ListToExcel<T>(List<T> data, string strHeaderText, string[] titles, string[] props = null, List<PorpTitle> ptlist = null) //{ // HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(strHeaderText.Replace('/', '&')); // PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); // #region 右击文件 属性信息 // { // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "NPOI"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "stander"; //填加xls文件作者信息 // si.ApplicationName = "stander"; //填加xls文件创建程序信息 // si.LastAuthor = "stander"; //填加xls文件最后保存者信息 // si.Comments = "stander"; //填加xls文件作者信息 // si.Title = strHeaderText; //填加xls文件标题信息 // si.Subject = strHeaderText;//填加文件主题信息 // si.CreateDateTime = System.DateTime.Now; // workbook.SummaryInformation = si; // } // #endregion // int rowheight = 25; // int colheight = 25 * 256; // #region 基础数据定义 // HSSFCellStyle cellstyle = (HSSFCellStyle)workbook.CreateCellStyle(); // cellstyle.BorderBottom = (BorderStyle)CellBorderType.THIN; // cellstyle.BorderLeft = (BorderStyle)CellBorderType.THIN; // cellstyle.BorderRight = (BorderStyle)CellBorderType.THIN; // cellstyle.BorderTop = (BorderStyle)CellBorderType.THIN; // cellstyle.Alignment = HorizontalAlignment.CENTER; // 居中 // cellstyle.VerticalAlignment = VerticalAlignment.CENTER; // //sheet.DefaultRowHeight = (short)rowheight; // //sheet.DefaultColumnWidth = colheight; // #endregion // #region 首行首列定义 // HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); // headerRow.HeightInPoints = rowheight;//行高 // if (strHeaderText.Contains("ATM Combination Code")) // { // headerRow.CreateCell(0).SetCellValue("Export Date:" + DateTime.Today.ToString("MM/dd/yyyy") + " " + strHeaderText); // } // else // { // headerRow.CreateCell(0).SetCellValue(strHeaderText); // } // HSSFCellStyle hvstyleh = (HSSFCellStyle)workbook.CreateCellStyle(); // hvstyleh.BorderBottom = (BorderStyle)CellBorderType.THIN; // hvstyleh.BorderLeft = (BorderStyle)CellBorderType.THIN; // hvstyleh.BorderRight = (BorderStyle)CellBorderType.THIN; // hvstyleh.BorderTop = (BorderStyle)CellBorderType.THIN; // hvstyleh.Alignment = HorizontalAlignment.CENTER; // 居中 // hvstyleh.VerticalAlignment = VerticalAlignment.CENTER; // 居中 // hvstyleh.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; // HSSFFont fonth = (HSSFFont)workbook.CreateFont(); // fonth.FontHeightInPoints = 20; // fonth.Boldweight = 700; // hvstyleh.SetFont(fonth); // CellRangeAddress m_region = new CellRangeAddress(0, 1, 0, titles.Count() - 1); //合并0列的n--n+2行 // sheet.AddMergedRegion(m_region); // setcelltag(m_region, hvstyleh, sheet); // headerRow.GetCell(0).CellStyle = hvstyleh; // var day = DateTime.Now; // int rowIndex = 2; // #endregion // #region sheet页的表头定义 // if (ptlist != null) // { // if (ptlist.Count > 0) // { // rowIndex = 3; // HSSFRow ttdataRow = (HSSFRow)sheet.CreateRow(rowIndex - 1); // ttdataRow.HeightInPoints = rowheight;//行高 // foreach (PorpTitle title in ptlist) // { // HSSFCell ttCell = (HSSFCell)ttdataRow.CreateCell(title.Poss); // ttCell.SetCellValue(title.TitleNM); // if (title.Poss != title.Pose) // { // CellRangeAddress m_region1 = new CellRangeAddress(2, 2, title.Poss, title.Pose); //合并0列的n--n+2行 // sheet.AddMergedRegion(m_region1); // setcelltag(m_region1, cellstyle, sheet); // ttCell.CellStyle = cellstyle; // } // else // { // ttCell.CellStyle = cellstyle; // } // } // } // } // #endregion // #region 表记录记录 // var jyzindex = rowIndex; // #region 定义表头 // int cellcnt = 0; // HSSFRow dataRow = (HSSFRow)sheet.CreateRow(jyzindex); // dataRow.HeightInPoints = rowheight;//行高 // HSSFCell newCell; // foreach (string prop in titles) // { // newCell = (HSSFCell)dataRow.CreateCell(cellcnt); // newCell.SetCellValue(prop); // newCell.CellStyle = cellstyle; // sheet.SetColumnWidth(cellcnt, colheight); // cellcnt++; // } // #endregion // #region 定义表内容 // jyzindex++; // foreach (T item in data) // { // dataRow = (HSSFRow)sheet.CreateRow(jyzindex); // dataRow.HeightInPoints = rowheight;//行高 // cellcnt = 0; // if (props != null) // { // foreach (string prop in props) // { // PropertyDescriptor prop1 = properties[prop]; // var val = ""; // try { val = ConvertHelper.ToStr(prop1.GetValue(item) ?? DBNull.Value); } // catch { } // newCell = (HSSFCell)dataRow.CreateCell(cellcnt); // newCell.SetCellValue(val); // newCell.CellStyle = cellstyle; // sheet.SetColumnWidth(cellcnt, colheight); // cellcnt++; // } // } // else // { // foreach (PropertyDescriptor prop in properties) // { // var val = ""; // try { val = ConvertHelper.ToStr(prop.GetValue(item) ?? DBNull.Value); } // catch { } // newCell = (HSSFCell)dataRow.CreateCell(cellcnt); // newCell.SetCellValue(val); // newCell.CellStyle = cellstyle; // sheet.SetColumnWidth(cellcnt, colheight); // cellcnt++; // } // } // jyzindex++; // } // #endregion // #endregion // System.IO.MemoryStream ms = new System.IO.MemoryStream(); // workbook.Write(ms); // return ms; //} //MemoryStream ms = NpoiToExcel.ListToExcel(list, LangResources.Resource.Menu_KeyPassword, titles, props, ptlist); //ms.Seek(0, SeekOrigin.Begin); //DateTime dt = TypeHelper.newdate(); //string dateTime = dt.ToString("yyMMdd");//yyMMddHHmmssfff //string fileName = LangResources.Resource.Menu_KeyPassword + dateTime + ".xls"; //return File(ms, "application/vnd.ms-excel", fileName); #endregion //以前项目 bs 没模板 创建内容 -- 写入服务器中(项目中) -- 根据项目中资源名称 -- 由Response导出 #region MyRegion //string filename = "资源信息导出_" + DateTime.Now.ToString("yyyyMMddHHmmss"); // string Title = "资源信息导出"; // string Page = "资源信息"; // //string filenamepath = CommonHelper.DataTableToExcel(ds.Tables[0], filename, false); // string filenamepath = NPOIOrgHelper.DataTableToExcel(ds.Tables[0], filename, Title, Page, false); // string relativepath = "/Uploads/Files/" + filename + ".xls"; // FileInfo fileInfo = new FileInfo(filenamepath); // Response.Clear(); // Response.ClearContent(); // Response.ClearHeaders(); // Response.AddHeader("Content-Disposition", "attachment;filename=" + fileInfo.Name); // Response.AddHeader("Content-Length", fileInfo.Length.ToString()); // Response.AddHeader("Content-Transfer-Encoding", "binary"); // Response.ContentType = "application/octet-stream"; // Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); // Response.WriteFile(fileInfo.FullName); // Response.Flush(); // Response.End(); // public static string DataTableToExcel(DataTable dataTable, string fileName, string Page, string Title, bool isShowExcle) //{ // //创建工作薄 // var workbook = new HSSFWorkbook(); // //创建表 // var table = workbook.CreateSheet(Page); // table.DefaultColumnWidth = 30; // table.AddMergedRegion(new CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1)); // table.SetColumnWidth(0, 15 * 256); // //字体 // IFont fontHeader1 = workbook.CreateFont(); // fontHeader1.FontName = "宋体"; // fontHeader1.FontHeightInPoints = 18; // fontHeader1.Boldweight = short.MaxValue; // IFont fontHeader2 = workbook.CreateFont(); // fontHeader2.FontName = "宋体"; // fontHeader2.FontHeightInPoints = 15; // fontHeader2.Boldweight = short.MaxValue; // fontHeader2.Color = 12; // IFont fontContent = workbook.CreateFont(); // fontContent.FontName = "宋体"; // fontContent.FontHeightInPoints = 13; // //样式 // var styleHeader1 = workbook.CreateCellStyle(); // styleHeader1.SetFont(fontHeader1); // styleHeader1.Alignment = HorizontalAlignment.Center; // styleHeader1.VerticalAlignment = VerticalAlignment.Center; // styleHeader1.BorderTop = BorderStyle.Thin; // styleHeader1.BorderBottom = BorderStyle.Thin; // styleHeader1.BorderLeft = BorderStyle.Thin; // styleHeader1.BorderRight = BorderStyle.Thin; // var styleHeader2 = workbook.CreateCellStyle(); // styleHeader2.SetFont(fontHeader2); // styleHeader2.Alignment = HorizontalAlignment.Center; // styleHeader2.VerticalAlignment = VerticalAlignment.Center; // styleHeader2.BorderTop = BorderStyle.Thin; // styleHeader2.BorderBottom = BorderStyle.Thin; // styleHeader2.BorderLeft = BorderStyle.Thin; // styleHeader2.BorderRight = BorderStyle.Thin; // var styleContent = workbook.CreateCellStyle(); // styleContent.SetFont(fontContent); // styleContent.Alignment = HorizontalAlignment.Center; // styleContent.VerticalAlignment = VerticalAlignment.Center; // styleContent.VerticalAlignment = VerticalAlignment.Center; // styleContent.BorderTop = BorderStyle.Thin; // styleContent.BorderBottom = BorderStyle.Thin; // styleContent.BorderLeft = BorderStyle.Thin; // styleContent.BorderRight = BorderStyle.Thin; // //表头 // var row0 = table.CreateRow(0); // row0.HeightInPoints = 35; // var cell0 = row0.CreateCell(0); // cell0.SetCellValue(Title); // cell0.CellStyle = styleHeader1; // var row1 = table.CreateRow(1); // row1.HeightInPoints = 30; // for (int i = 0; i < dataTable.Columns.Count; i++) // { // var cell = row1.CreateCell(i); // cell.SetCellValue(dataTable.Columns[i].ColumnName); // cell.CellStyle = styleHeader2; // } // //dataTable数据写入 // for (var i = 0; i < dataTable.Rows.Count; i++) // { // var row = table.CreateRow(i + 2); // row.HeightInPoints = 26; // for (int j = 0; j < dataTable.Columns.Count; j++) // { // var cell = row.CreateCell(j); // cell.SetCellValue(dataTable.Rows[i][j].ToString()); // cell.CellStyle = styleContent; // } // } // fileName = HttpContext.Current.Server.MapPath("/Uploads/Files/") + fileName + ".xls"; // if (File.Exists(fileName)) // { // return fileName; // } // //打开xls文件，如没有则创建，如存在则在创建是不要打开该文件 // using (var fs = File.OpenWrite(fileName)) // { // workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。 // Console.WriteLine("生成成功"); // } // return fileName; //} // public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, List<string> strSubTexts = null, List<string> strFootTexts = null) //{ // HttpContext curContext = HttpContext.Current; // // 设置编码和附件格式 // curContext.Response.ContentType = "application/vnd.ms-excel"; // curContext.Response.ContentEncoding = Encoding.UTF8; // curContext.Response.Charset = ""; // curContext.Response.AppendHeader("Content-Disposition", // "attachment;filename=" + strFileName); // curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, strSubTexts, strFootTexts).GetBuffer()); // curContext.Response.End(); //} #endregion // bs 导出 zip 不管有无模板 创建内容 -- 服务器上创建无内容zip(zip输出流) -- 创建zip实体(将实体 放入服务器zip 向实体写入内容 zip就有了内容) 关闭流 -- -- -- 导出zip #region MyRegion // public ActionResult ExcelPrint(string bgrq, string endrq, string jijubianhao, string customerName, string status, string numMin, string numMax) //{ // //new AutoEmailATMPWD(null, null, null).RunExport(); // //return null; // #region MyRegion // var user = _isysUserService.GetById(_userInfo.UserId); // int? _numMin = string.IsNullOrEmpty(numMin) ? null : (int?)int.Parse(numMin); // int? _numMax = string.IsNullOrEmpty(numMax) ? null : (int?)int.Parse(numMax); // DateTime? _BgRq = string.IsNullOrEmpty(bgrq) ? null : (DateTime?)Convert.ToDateTime(bgrq); // DateTime? _EndRq = string.IsNullOrEmpty(endrq) ? null : (DateTime?)Convert.ToDateTime(endrq); // string Location = Request["Location"]; // int pagecnt, thresholdCount; // List<VMSelectItem> list = _IAtmAtmService.GetDataList(_BgRq, _EndRq, jijubianhao, customerName, status, 0, 0, out pagecnt, "", "", out thresholdCount, _numMin, _numMax, "export", Location); // string[] titles = { "Atm ID", "Location Name", "Customer", "Old Password", "Current Password", "Change By", "Change Time" }; // string[] props = { "One", "BeiZhu", "Remark", "Three", "Two", "UserName", "banci" }; // List<PorpTitle> ptlist = new List<PorpTitle>(); // string _BranchCode = _ISysProjectService.GetProjectNameById(_userInfo.ProjectId); // string title = "ATM Combination Code-" + _BranchCode + " Export by: " + user.UserName; // DateTime dt = DateTime.Now; // string dateTime = dt.ToString("yyMMdd");//yyMMddHHmmssfff // string dateTimess = dt.ToString("yyMMddHHmmssfff");//yyMMddHHmmssfff // MemoryStream ms = this.ListToExcel2(user.UserName, "CC.xls", list, title, titles, props, ptlist); // //MemoryStream ms = NpoiToExcel.ListToExcel(list, title, titles, props, ptlist); // ms.Seek(0, SeekOrigin.Begin); // string fileName = "ATM Combination Code_" + _BranchCode + dateTime + ".xls"; // return File(ms, "application/vnd.ms-excel", fileName); // #endregion // //string pass = "COMBI00"; // //var syspro = _ISysProjectService.GetById(_userInfo.ProjectId); // //pass = syspro == null ? pass : "COMBI" + syspro.Code; // //string outfileName = "ATM Combination Code_" + _BranchCode + dateTime + ".xls"; // //string fileName = "ATM Combination Code_" + _BranchCode + dateTimess + ".zip"; // //string fileNameout = "ATM Combination Code_" + _BranchCode + dateTime + ".zip"; // //MemoryStream ms = this.ListToExcel2("CC.xls", list, title, titles, props, ptlist); // //string strpath = Server.MapPath("/Upload/Combination/" + fileName); // //Common.ZipUtil.ZipFileMain(ms.ToArray(), strpath, pass, outfileName); // //return File(strpath, "application/octet-stream", fileNameout); //} // /// <summary> ///// 文件加密压缩 ///// </summary> ///// <param name="FileToZip">需要压缩的文件路径</param> ///// <param name="ZipedFile">压缩包路径（压缩包文件类型看自己需求）</param> ///// <param name="password">加密密码</param> //public static void ZipFileMain(byte[] buffer, string ZipedFile, string password,string outname) //{ // ZipOutputStream s = new ZipOutputStream(File.Create(ZipedFile)); //ZipedFile里面没内容(zip) // s.SetLevel(6); // 0 - store only to 9 - means best compression // s.Password = password; // //打开压缩文件 // //FileStream fs = File.OpenRead(FileToZip); // //byte[] buffer = new byte[fs.Length]; // //fs.Read(buffer, 0, buffer.Length); // //Array arr = FileToZip.Split('\\'); // //string le = arr.GetValue(arr.Length - 1).ToString(); // ZipEntry entry = new ZipEntry(outname); // entry.DateTime = DateTime.Now; // entry.Size = buffer.Length; // s.PutNextEntry(entry); // s.Write(buffer, 0, buffer.Length); // s.Finish(); // s.Close(); // //Stream output = (Stream)s; // //return output; //} #endregion } } }
public void GetRouteTotalExport2(string line, string startdate, string RouteName, string TotalStates, List<string> branchid) { string[] ab = { "1" }; SortByDocument sbd = new SortByDocument(); IMongoQuery query = null; List<IMongoQuery> list = new List<IMongoQuery>(); if (!string.IsNullOrEmpty(startdate)) { list.Add(Query.GTE("JobDate", DateTime.Parse(startdate))); list.Add(Query.LT("JobDate", DateTime.Parse(startdate).AddDays(1))); } if (!string.IsNullOrEmpty(RouteName)) { list.Add(Query.Matches("RouteName", RouteName)); } if (branchid != null && branchid.Count > 0 && branchid[0] != "Please Select") { list.Add(Query.Matches("Branch", branchid[0])); } query = Query.And(list); List<RouteTotal> task = Common.MongodbHelper.FindListBy<RouteTotal>(query, sbd, "table_RouteTotal"); string templetpath = System.Configuration.ConfigurationSettings.AppSettings["TempletsPath"].ToString(); string FileFullPath = Server.MapPath(templetpath) + "RouteTotal.xlsx"; if (!System.IO.File.Exists(FileFullPath)) { return; } XSSFWorkbook workbook = new XSSFWorkbook(); using (FileStream file = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(file); //(XSSFWorkbook)WorkbookFactory.Create(file); } //workbook.("new sheet"); XSSFSheet sheet1 = (XSSFSheet)workbook.GetSheetAt(0); //XSSFSheet sheet1 = (XSSFSheet)workbook.CreateSheet("asdasdasd"); //workbook.RemoveSheetAt(0); //ISheet sheet9 = (XSSFSheet)workbook.CloneSheet(0); //HSSFSheet sheet0 = (HSSFSheet)workbook.GetSheetAt(0); //XSSFSheet sheet1 = (XSSFSheet)(ISheet)sheet0.CopySheet(true); //ISheet sheet1 = workbook.CloneSheet(0); workbook.RemoveSheetAt(0); int data4 = 4; //CreateRow(sheet1, 0); //CreateRow(sheet1, 1); //CreateRow(sheet1, 2); //CreateRow(sheet1, 3); //CreateRow(sheet1, 4); //CreateRow(sheet1, 5); IRow row; if (task.Count > 1) { row = sheet1.GetRow(4); // NPOIHelper.CopyRowx(sheet1, 5, 4, task.Count); // } //HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); //format.GetFormat("h:mm"); string forat = ""; foreach (var item in task) { CreateCell(sheet1, data4, 0); IRow row0 = (IRow)sheet1.GetRow(data4); row0.Height = 40 * 20; ((IRow)sheet1.GetRow(data4)).GetCell(0).SetCellValue(data4 - 3); CreateCell(sheet1, data4, 1); ((IRow)sheet1.GetRow(data4)).GetCell(1).SetCellValue(item.JobDate.GetValueOrDefault().ToString("yyyy-MM-dd")); CreateCell(sheet1, data4, 2); ((IRow)sheet1.GetRow(data4)).GetCell(2).SetCellValue(item.Branch); CreateCell(sheet1, data4, 3); ((IRow)sheet1.GetRow(data4)).GetCell(3).SetCellValue(item.RouteName); CreateCell(sheet1, data4, 4); ((IRow)sheet1.GetRow(data4)).GetCell(4).SetCellValue(item.CrewLeaderScanIn != null ? item.CrewLeaderScanIn.Value.ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 5); ((IRow)sheet1.GetRow(data4)).GetCell(5).SetCellValue(item.CrewLeaderScanOut != null ? item.CrewLeaderScanOut.Value.ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 6); ((IRow)sheet1.GetRow(data4)).GetCell(6).SetCellValue(item.CrewLeader); CreateCell(sheet1, data4, 7); ((IRow)sheet1.GetRow(data4)).GetCell(7).SetCellValue(item.CrewDriverScanIn != null ? item.CrewDriverScanIn.Value.ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 8); ((IRow)sheet1.GetRow(data4)).GetCell(8).SetCellValue(item.CrewDriverScanOut != null ? item.CrewDriverScanOut.Value.ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 9); ((IRow)sheet1.GetRow(data4)).GetCell(9).SetCellValue(item.CrewDriver); CreateCell(sheet1, data4, 10); ((IRow)sheet1.GetRow(data4)).GetCell(10).SetCellValue(item.CrewMemberScanIn != null ? item.CrewMemberScanIn.Value.ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 11); ((IRow)sheet1.GetRow(data4)).GetCell(11).SetCellValue(item.CrewMemberScanOut != null ? item.CrewMemberScanOut.Value.ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 12); ((IRow)sheet1.GetRow(data4)).GetCell(12).SetCellValue(item.CrewMember); CreateCell(sheet1, data4, 13); //((IRow)sheet1.GetRow(data4)).GetCell(13).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("h:mm"); //((IRow)sheet1.GetRow(data4)).GetCell(13).SetCellType(CellType.FORMULA); ((IRow)sheet1.GetRow(data4)).GetCell(13).SetCellValue(item.Rosteredstart != null ? item.Rosteredstart.GetValueOrDefault().ToString("HH:mm:ss") : ""); //((IRow)sheet1.GetRow(data4)).GetCell(13).SetCellFormula(item.Rosteredstart != null ? item.Rosteredstart.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 14); ((IRow)sheet1.GetRow(data4)).GetCell(14).SetCellValue(item.Inbaytime != null ? item.Inbaytime.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 15); ((IRow)sheet1.GetRow(data4)).GetCell(15).SetCellValue(item.Outbaytime != null ? item.Outbaytime.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 16); ((IRow)sheet1.GetRow(data4)).GetCell(16).SetCellType(CellType.FORMULA); forat = @"IF(O{0}="""","""",O{0}-N{0})"; //forat = @"IF(O{0}="""","""",TEXT(O{0}-N{0},""[hh]:mm:ss""))"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(16).SetCellFormula(forat); CreateCell(sheet1, data4, 17); ((IRow)sheet1.GetRow(data4)).GetCell(17).SetCellType(CellType.FORMULA); forat = @"IF(P{0}="""","""",P{0}-O{0})"; //forat = @"IF(P{0}="""","""",TEXT(P{0}-O{0},""[hh]:mm:ss""))"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(17).SetCellFormula(forat); CreateCell(sheet1, data4, 18); ((IRow)sheet1.GetRow(data4)).GetCell(18).SetCellValue(item.Trip1_Depart != null ? item.Trip1_Depart.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 19); ((IRow)sheet1.GetRow(data4)).GetCell(19).SetCellValue(item.Trip1_Return != null ? item.Trip1_Return.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 20); ((IRow)sheet1.GetRow(data4)).GetCell(20).SetCellValue(item.Trip2_Depart != null ? item.Trip2_Depart.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 21); ((IRow)sheet1.GetRow(data4)).GetCell(21).SetCellValue(item.Trip2_Return != null ? item.Trip2_Return.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 22); ((IRow)sheet1.GetRow(data4)).GetCell(22).SetCellValue(item.Trip3_Depart != null ? item.Trip3_Depart.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 23); ((IRow)sheet1.GetRow(data4)).GetCell(23).SetCellValue(item.Trip3_Return != null ? item.Trip3_Return.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 24); ((IRow)sheet1.GetRow(data4)).GetCell(24).SetCellValue(item.Trip4_Depart != null ? item.Trip4_Depart.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 25); ((IRow)sheet1.GetRow(data4)).GetCell(25).SetCellValue(item.Trip4_Return != null ? item.Trip4_Return.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 26); ((IRow)sheet1.GetRow(data4)).GetCell(26).SetCellValue(item.Trip5_Depart != null ? item.Trip5_Depart.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 27); ((IRow)sheet1.GetRow(data4)).GetCell(27).SetCellValue(item.Trip5_Return != null ? item.Trip5_Return.GetValueOrDefault().ToString("HH:mm:ss") : ""); CreateCell(sheet1, data4, 28); ((IRow)sheet1.GetRow(data4)).GetCell(28).SetCellValue(item.Returnbaytime.GetValueOrDefault().ToString("HH:mm:ss")); CreateCell(sheet1, data4, 29); ((IRow)sheet1.GetRow(data4)).GetCell(29).SetCellValue(item.Runfinish.GetValueOrDefault().ToString("HH:mm:ss")); CreateCell(sheet1, data4, 30); ((IRow)sheet1.GetRow(data4)).GetCell(30).SetCellType(CellType.FORMULA); forat = @"IFERROR(IF(AD{0}-N{0}=0,"""",(AD{0}-N{0})+(AD{0}<N{0})),"""")"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(30).SetCellFormula(forat); // CreateCell(sheet1, data4, 31); ((IRow)sheet1.GetRow(data4)).GetCell(31).SetCellType(CellType.FORMULA); forat = @"IF(P{0}="""","""",P{0}-N{0})"; //forat = @"IF(P{0}="""","""",TEXT(P{0}-N{0},""[hh]:mm:ss""))"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(31).SetCellFormula(forat); CreateCell(sheet1, data4, 32); ((IRow)sheet1.GetRow(data4)).GetCell(32).SetCellType(CellType.FORMULA); forat = @"IF(AE{0}="""","""",AD{0}-AC{0})"; //forat = @"IF(AE{0}="""","""",TEXT(AD{0}-AC{0},""[hh]:mm:ss""))"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(32).SetCellFormula(forat); //60 CreateCell(sheet1, data4, 33); ((IRow)sheet1.GetRow(data4)).GetCell(33).SetCellValue("60"); CreateCell(sheet1, data4, 34); ((IRow)sheet1.GetRow(data4)).GetCell(34).SetCellValue(item.Crewno); CreateCell(sheet1, data4, 35); ((IRow)sheet1.GetRow(data4)).GetCell(35).SetCellValue(item.Jobs_completed); CreateCell(sheet1, data4, 36); ((IRow)sheet1.GetRow(data4)).GetCell(36).SetCellValue(item.Jobsnotcompleted); CreateCell(sheet1, data4, 37); ((IRow)sheet1.GetRow(data4)).GetCell(37).SetCellType(CellType.FORMULA); forat = @"AJ{0}+AK{0}"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(37).SetCellFormula(forat); CreateCell(sheet1, data4, 38); ((IRow)sheet1.GetRow(data4)).GetCell(38).SetCellType(CellType.FORMULA); forat = @"IF(AF{0}="""","""",HOUR(AF{0})+(MINUTE(AF{0})/60)-(AI{0}/60))"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(38).SetCellFormula(forat); CreateCell(sheet1, data4, 39); ((IRow)sheet1.GetRow(data4)).GetCell(39).SetCellType(CellType.FORMULA); forat = @"IF(AF{0}="""","""",HOUR(AF{0})+(MINUTE(AF{0})/60))"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(39).SetCellFormula(forat); CreateCell(sheet1, data4, 40); ((IRow)sheet1.GetRow(data4)).GetCell(40).SetCellType(CellType.FORMULA); forat = @"IFERROR(AM{0}/AL{0},"""")"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(40).SetCellFormula(forat); CreateCell(sheet1, data4, 41); ((IRow)sheet1.GetRow(data4)).GetCell(41).SetCellType(CellType.FORMULA); forat = @"IFERROR(AL{0}/AM{0},"""")"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(41).SetCellFormula(forat); CreateCell(sheet1, data4, 42); ((IRow)sheet1.GetRow(data4)).GetCell(42).SetCellValue(item.TruckNumber); CreateCell(sheet1, data4, 43); ((IRow)sheet1.GetRow(data4)).GetCell(43).SetCellValue(item.StartKms); CreateCell(sheet1, data4, 44); ((IRow)sheet1.GetRow(data4)).GetCell(44).SetCellValue(item.FinishKms); CreateCell(sheet1, data4, 45); ((IRow)sheet1.GetRow(data4)).GetCell(45).SetCellType(CellType.FORMULA); forat = @"IF(AS{0}="""","""",AS{0}-AR{0})"; //forat = @"IF(AS{0}="""","""",TEXT(AS{0}-AR{0},""[hh]:mm:ss""))"; forat = string.Format(forat, (data4 + 1)); ((IRow)sheet1.GetRow(data4)).GetCell(45).SetCellFormula(forat); CreateCell(sheet1, data4, 46); ((IRow)sheet1.GetRow(data4)).GetCell(46).SetCellValue(item.Comments); data4++; } sheet1.ForceFormulaRecalculation = true; workbook.SetForceFormulaRecalculation(true); //System.IO.MemoryStream ms = new System.IO.MemoryStream(); //workbook.Write(ms); ////ms.Seek(0, SeekOrigin.Begin); //ms.Flush(); //ms.Position = 0; //string dateTime = DateTime.Today.ToString("ddMMyyyy");//yyMMddHHmmssfff //string fileName = "Route Total " + dateTime + ".xlsx"; //return File(ms, "application/vnd.ms-excel", fileName); //using (MemoryStream ms = new MemoryStream()) //{ //var buffer = ms.GetBuffer(); //ms.Close(); string dateTime = DateTime.Today.ToString("ddMMyyyy");//yyMMddHHmmssfff string fileName = "Route Total " + dateTime + ".xlsx"; string fileNames = Server.MapPath("/Upload/file/") + fileName; using (var ms = new MemoryStream()) { workbook.Write(ms); //向打开的这个xls文件中写入mySheet表并保存。 //using (var ms = new MemoryStream()) //{ //FileInfo fileInfo = new FileInfo(fileNames); //workbook.Write(ms); long fileSize = ms.ToArray().Length; Response.Clear(); Response.ClearContent(); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName); Response.AddHeader("Content-Length", fileSize.ToString()); Response.AddHeader("Content-Transfer-Encoding", "binary"); Response.ContentType = "application/octet-stream"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); //Response.WriteFile(fileInfo.FullName); Response.BinaryWrite(ms.GetBuffer()); Response.Flush(); Response.End(); //ms.Close(); } //return null; //return File(buffer, "application/ms-excel", fileName); //return File(buffer, fileName); //return FileContentResult(buffer, fileName); //} } 不用保存服务器 直接生成流 根据 Response导出 mvc File里面 转成流 输出 Response
