void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt) { NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(item.Caption); index++; } // 添加数据 for (int i = 0; i < dt.Rows.Count; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(dt.Rows[i][item].ToString()); index++; } } }
public static void SetCellValue(NPOI.SS.UserModel.ICell iCell, object cellValue) { if (iCell == null) { return; } if (cellValue != null) { if (cellValue.GetType() == typeof(DBNull)) { iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank); } else if (cellValue.GetType() == typeof(bool)) { iCell.SetCellValue((bool)cellValue); } else if (cellValue.GetType() == typeof(string)) { if (cellValue.ToString() == "#NUM!") { iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank); } else { iCell.SetCellValue((string)cellValue); } } else if (cellValue.GetType() == typeof(DateTime)) { NPOI.SS.UserModel.ICellStyle iCellStyle = iCell.Sheet.Workbook.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat iDataFormat = iCell.Sheet.Workbook.CreateDataFormat(); iCellStyle.DataFormat = iDataFormat.GetFormat("yyyy/MM/dd HH:mm:ss"); iCell.CellStyle = iCellStyle; iCell.SetCellValue((DateTime)cellValue); } else if (cellValue.GetType() == typeof(int) || cellValue.GetType() == typeof(double) || cellValue.GetType() == typeof(float)) { iCell.SetCellValue((double)cellValue); } else { iCell.SetCellValue(cellValue.ToString()); } } }
public void Export(Stream ms, DataTable dt, string postfix) { //var fileName = m_Handler.GetFileName(); NPOI.SS.UserModel.IWorkbook book = null; if (postfix == ".xls") { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else if (postfix == ".xlsx") { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else { throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名"); } m_Handler.Encode(ref dt); //WriteExcel(ref book, dt); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(item.Caption); index++; } // 添加数据 for (int i = 0; i < dt.Rows.Count; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(dt.Rows[i][item].ToString()); index++; } } //// 写入 //MemoryStream ms = new MemoryStream(); book.Write(ms); book = null; //using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write)) //{ // byte[] data = ms.ToArray(); // fs.Write(data, 0, data.Length); // fs.Flush(); //} //ms.Close(); //ms.Dispose(); }
private void explort() { SaveFileDialog sflg = new SaveFileDialog(); sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } //this.gridView1.ExportToXls(sflg.FileName); //NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.IWorkbook book = null; if (sflg.FilterIndex == 1) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_001"); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; foreach (DataGridViewColumn item in this.dataGridView2.Columns) { if (item.Visible) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(item.HeaderText); index++; } } // 添加数据 for (int i = 0; i < this.dataGridView2.Rows.Count; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (DataGridViewColumn item in this.dataGridView2.Columns) { if (item.Visible) { if (dataGridView2.Rows[i].Cells[item.HeaderText].Value != null) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(dataGridView2.Rows[i].Cells[item.HeaderText].Value.ToString().Trim()); } index++; } } } // 写入 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); book = null; using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } ms.Close(); ms.Dispose(); }
private void sb_save_Click(object sender, EventArgs e) { SaveFileDialog sflg = new SaveFileDialog(); sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } int aa = 0; NPOI.SS.UserModel.IWorkbook book = null; if (sflg.FilterIndex == 1) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } foreach (XtraTabPage trp in this.xtraTabControl1.TabPages) { XtraTabPage xinka = trp; try { this.active_nepCalaTable = xinka.Controls[0] as NepCalaTable; } catch (Exception err) { MessageBox.Show("出现了错误: " + err.Message); } aa += 1; //this.gridView1.ExportToXls(sflg.FileName); //NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(string.Format("test_{0}", aa)); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(xinka.Text); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; GridView ac = this.active_nepCalaTable.GC; int count = ac.Columns.Count; for (int i = 0; i < count; i++) { string Caption = ac.Columns[i].Caption; NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(Caption); index++; } // 添加数据 DataTable dt = this.active_nepCalaTable.DDT; for (int j = 0; j < dt.Rows.Count; j++) { index = 0; row = sheet.CreateRow(j + 1); for (int k = 0; k < count; k++) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); //cell.SetCellValue(this.gridView1.GetRowCellValue(i, item).ToString()); cell.SetCellValue(dt.Rows[j][k].ToString()); index++; } } NPOI.SS.UserModel.IRow r1 = sheet.GetRow(0); // 第一排 NPOI.SS.UserModel.ICell cell1 = r1.CreateCell(12); cell1.SetCellType(NPOI.SS.UserModel.CellType.String); cell1.SetCellValue("激发点距"); NPOI.SS.UserModel.ICell cell11 = r1.CreateCell(13); cell11.SetCellType(NPOI.SS.UserModel.CellType.String); cell11.SetCellValue(this.active_nepCalaTable.jifa_dianju.Text); // 第二排 r1 = sheet.GetRow(1); NPOI.SS.UserModel.ICell cell2 = r1.CreateCell(12); cell2.SetCellType(NPOI.SS.UserModel.CellType.String); cell2.SetCellValue("接收点距"); NPOI.SS.UserModel.ICell cell21 = r1.CreateCell(13); cell21.SetCellType(NPOI.SS.UserModel.CellType.String); cell21.SetCellValue(this.active_nepCalaTable.jieshou_dianju.Text); // 第三排 r1 = sheet.GetRow(2); NPOI.SS.UserModel.ICell cell3 = r1.CreateCell(12); cell3.SetCellType(NPOI.SS.UserModel.CellType.String); cell3.SetCellValue("接收点数"); NPOI.SS.UserModel.ICell cell31 = r1.CreateCell(13); cell31.SetCellType(NPOI.SS.UserModel.CellType.String); cell31.SetCellValue(this.active_nepCalaTable.jieshou_dianshu.Text); // 第四排 r1 = sheet.GetRow(3); NPOI.SS.UserModel.ICell cell4 = r1.CreateCell(12); cell4.SetCellType(NPOI.SS.UserModel.CellType.String); cell4.SetCellValue("激发线距"); NPOI.SS.UserModel.ICell cell41 = r1.CreateCell(13); cell41.SetCellType(NPOI.SS.UserModel.CellType.String); cell41.SetCellValue(this.active_nepCalaTable.jifa_xianju.Text); // 第五排 r1 = sheet.GetRow(4); NPOI.SS.UserModel.ICell cell5 = r1.CreateCell(12); cell5.SetCellType(NPOI.SS.UserModel.CellType.String); cell5.SetCellValue("接受线距"); NPOI.SS.UserModel.ICell cell51 = r1.CreateCell(13); cell51.SetCellType(NPOI.SS.UserModel.CellType.String); cell51.SetCellValue(this.active_nepCalaTable.jieshou_xianju.Text); // 第六排 r1 = sheet.GetRow(5); NPOI.SS.UserModel.ICell cell6 = r1.CreateCell(12); cell6.SetCellType(NPOI.SS.UserModel.CellType.String); cell6.SetCellValue("纵向滚动距离"); NPOI.SS.UserModel.ICell cell61 = r1.CreateCell(13); cell61.SetCellType(NPOI.SS.UserModel.CellType.String); cell61.SetCellValue(this.active_nepCalaTable.muban_zong.Text); // 第7排 r1 = sheet.GetRow(6); NPOI.SS.UserModel.ICell cell7 = r1.CreateCell(12); cell7.SetCellType(NPOI.SS.UserModel.CellType.String); cell7.SetCellValue("模板炮"); NPOI.SS.UserModel.ICell cell71 = r1.CreateCell(13); cell71.SetCellType(NPOI.SS.UserModel.CellType.String); cell71.SetCellValue(this.active_nepCalaTable.mobanpao.Text); // 第8排 r1 = sheet.GetRow(7); NPOI.SS.UserModel.ICell cell8 = r1.CreateCell(12); cell8.SetCellType(NPOI.SS.UserModel.CellType.String); cell8.SetCellValue("接收线数"); NPOI.SS.UserModel.ICell cell81 = r1.CreateCell(13); cell81.SetCellType(NPOI.SS.UserModel.CellType.String); cell81.SetCellValue(this.active_nepCalaTable.jieshou_xianshu.Text); // 第9排 r1 = sheet.GetRow(8); NPOI.SS.UserModel.ICell cell9 = r1.CreateCell(12); cell9.SetCellType(NPOI.SS.UserModel.CellType.String); cell9.SetCellValue("横向滚动距离"); NPOI.SS.UserModel.ICell cell91 = r1.CreateCell(13); cell91.SetCellType(NPOI.SS.UserModel.CellType.String); cell91.SetCellValue(this.active_nepCalaTable.muban_heng.Text); // 第10排 r1 = sheet.GetRow(9); NPOI.SS.UserModel.ICell cell10 = r1.CreateCell(12); cell10.SetCellType(NPOI.SS.UserModel.CellType.String); cell10.SetCellValue("布设接受线数"); NPOI.SS.UserModel.ICell cell101 = r1.CreateCell(13); cell101.SetCellType(NPOI.SS.UserModel.CellType.String); cell101.SetCellValue(this.active_nepCalaTable.bushe_jieshouxianshu.Text); // 第11排 r1 = sheet.GetRow(10); NPOI.SS.UserModel.ICell cell1_1 = r1.CreateCell(12); cell1_1.SetCellType(NPOI.SS.UserModel.CellType.String); cell1_1.SetCellValue("布设激发线数"); NPOI.SS.UserModel.ICell cell111 = r1.CreateCell(13); cell111.SetCellType(NPOI.SS.UserModel.CellType.String); cell111.SetCellValue(this.active_nepCalaTable.bushe_jifaxianshu.Text); // 写入 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); //book = null; using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } ms.Close(); ms.Dispose(); } book = null; MessageBox.Show("保存成功了"); }
public void DownlodExcel() { SaveFileDialog sflg = new SaveFileDialog(); sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; sflg.FileName = "采购单细节导入模板表.xls"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } NPOI.SS.UserModel.IWorkbook book = null; if (sflg.FilterIndex == 1) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("采购细节表"); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = row.CreateCell(0); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue("品名"); #region 医疗器械(这里不用) //if ((int)this.toolStripComboBox1.ComboBox.SelectedValue == (int)PurchaseDrugTypes.医疗器械) //{ // cell = row.CreateCell(1); // cell.SetCellType(NPOI.SS.UserModel.CellType.STRING); // cell.SetCellValue("型号"); // cell = row.CreateCell(2); // cell.SetCellType(NPOI.SS.UserModel.CellType.STRING); // cell.SetCellValue("规格"); // cell = row.CreateCell(3); // cell.SetCellType(NPOI.SS.UserModel.CellType.STRING); // cell.SetCellValue("单位"); // cell = row.CreateCell(4); // cell.SetCellType(NPOI.SS.UserModel.CellType.STRING); // cell.SetCellValue("生产厂家"); // cell = row.CreateCell(5); // cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC); // cell.SetCellValue("数量"); // cell = row.CreateCell(6); // cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC); // cell.SetCellValue("单价"); // cell = row.CreateCell(7); // cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC); // cell.SetCellValue("税率(%)"); //} //else #endregion { cell = row.CreateCell(1); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue("剂型"); cell = row.CreateCell(2); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue("规格"); cell = row.CreateCell(3); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue("单位"); cell = row.CreateCell(4); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue("生产厂家"); cell = row.CreateCell(5); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue("产地"); cell = row.CreateCell(6); cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell.SetCellValue("数量"); cell = row.CreateCell(7); cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell.SetCellValue("单价"); cell = row.CreateCell(8); cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell.SetCellValue("税率(%)"); } // 写入 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); book = null; using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } ms.Close(); ms.Dispose(); }
private async void executeExportCommand(object obj) { IsRunning = true; await Task.Run(() => { try { reader.CheckPermission(); var filePath = Path.Combine(reader.GetFolderPath(), "SmsExport-" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xlsx"); var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet("Sheet1"); //第一行 { var row = sheet.CreateRow(0); var cColumnInd = 0; row.CreateCell(cColumnInd).SetCellValue("编号"); cColumnInd++; row.CreateCell(cColumnInd).SetCellValue("号码"); cColumnInd++; row.CreateCell(cColumnInd).SetCellValue("时间"); cColumnInd++; row.CreateCell(cColumnInd).SetCellValue("类型"); cColumnInd++; row.CreateCell(cColumnInd).SetCellValue("内容"); cColumnInd++; } //当前行号 var cRowInd = 1; var totalCount = reader.GetCount(StartIndex); var enumerator = reader.GetEnumerator(StartIndex); var index = 0; while (enumerator.MoveNext()) { index++; var item = enumerator.Current; var typeStr = ""; switch (item.ItemType) { case SmsItem.SmsItemType.Send: typeStr = "发送"; break; case SmsItem.SmsItemType.Recv: typeStr = "接收"; break; default: continue; } { var cColumnInd = 0; var row = sheet.CreateRow(cRowInd); NPOI.SS.UserModel.ICell cell = null; cell = row.CreateCell(cColumnInd); cell.SetCellValue(item.Id); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cColumnInd++; cell = row.CreateCell(cColumnInd); cell.SetCellValue(item.Address); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cColumnInd++; cell = row.CreateCell(cColumnInd); cell.SetCellValue(item.Date.ToString("yyyy-MM-dd HH:mm:ss")); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cColumnInd++; cell = row.CreateCell(cColumnInd); cell.SetCellValue(typeStr); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cColumnInd++; cell = row.CreateCell(cColumnInd); cell.SetCellValue(item.Body); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cColumnInd++; } cRowInd++; Progress = index * 1D / totalCount; Message = $"读取短信[{index} / {totalCount}]中..."; } Message = $"正在保存到文件..."; using (var stream = File.OpenWrite(filePath)) workbook.Write(stream); Message = $"已保存到{filePath}"; } catch (Exception ex) { Message = "错误," + ex; } }); IsRunning = false; }
/// <summary> /// 将excel文件内容读取到DataTable数据表中 /// </summary> /// <param name="fileName">文件完整路径名</param> /// <param name="sheetName">指定读取excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名:true=是,false=否</param> /// <returns>DataTable数据表</returns> public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true) { //定义要返回的datatable对象 DataTable data = new DataTable(); //excel工作表 NPOI.SS.UserModel.ISheet sheet = null; //数据开始行(排除标题行) int startRow = 0; try { if (!File.Exists(fileName)) { return(null); } //根据指定路径读取文件 FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); //根据文件流创建excel数据结构 NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); //IWorkbook workbook = new HSSFWorkbook(fs); //如果有指定工作表名称 if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet if (sheet == null) { sheet = workbook.GetSheetAt(0); } } else { //如果没有指定的sheetName,则尝试获取第一个sheet sheet = workbook.GetSheetAt(0); } if (sheet != null) { NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0); //一行最后一个cell的编号 即总的列数 int cellCount = firstRow.LastCellNum; //如果第一行是标题列名 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i); cell.SetCellType(NPOI.SS.UserModel.CellType.String); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { dataRow[j] = row.GetCell(j).ToString(); } } data.Rows.Add(dataRow); } } return(data); } catch (Exception ex) { throw ex; } }
public String ExportProductsList([FromUri] String id = "") { String url = Request.RequestUri.ToString(); String fileUrl = ""; List <Product> productsList = new List <Product>(); if (id.Trim().Length == 0) { productsList = getFullList(); fileUrl = url.Replace("api/ExportProductsList/", "templates/"); url = url.Replace("api/ExportProductsList/", "Content/imgs/"); } else { productsList = getFilteredList(id); fileUrl = url.Replace("api/ExportProductsList/" + id, "templates/"); url = url.Replace("api/ExportProductsList/" + id, "Content/imgs/"); } String filename = DateTime.Now.ToString("yyyyMMddHHmmss") + "_products.xls"; String filepath = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/templates"), filename); String filepathtemplate = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/templates"), "template.xls"); fileUrl = fileUrl + filename; var fs = new FileStream(filepathtemplate, FileMode.Open, FileAccess.Read); var templateWorkbook = new HSSFWorkbook(fs, true); NPOI.SS.UserModel.ISheet sheet = templateWorkbook.GetSheet("ProductsList"); int i = 1; foreach (Product product in productsList) { NPOI.SS.UserModel.IRow dataRow = sheet.GetRow(i); NPOI.SS.UserModel.ICell cell0 = dataRow.GetCell(0); cell0.SetCellType(NPOI.SS.UserModel.CellType.String); cell0.SetCellValue(product.Id); NPOI.SS.UserModel.ICell cell1 = dataRow.GetCell(1); cell1.SetCellType(NPOI.SS.UserModel.CellType.String); cell1.SetCellValue(product.Code); NPOI.SS.UserModel.ICell cell2 = dataRow.GetCell(2); cell2.SetCellType(NPOI.SS.UserModel.CellType.String); cell2.SetCellValue(product.Name); NPOI.SS.UserModel.ICell cell3 = dataRow.GetCell(3); cell3.SetCellType(NPOI.SS.UserModel.CellType.String); var tempURL = ""; if (product.Photo.Trim().Length > 0) { tempURL = url + product.Photo; } cell3.SetCellValue(tempURL); NPOI.SS.UserModel.ICell cell4 = dataRow.GetCell(4); cell4.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell4.SetCellValue(product.Price); NPOI.SS.UserModel.ICell cell5 = dataRow.GetCell(5); cell5.SetCellType(NPOI.SS.UserModel.CellType.String); cell5.SetCellValue(product.LastUpdate.ToString("yyyyMMdd HH:mm:ss")); i += 1; } try { File.Delete(filepath); } catch { } var memoryStream = new MemoryStream(); templateWorkbook.Write(memoryStream); Byte[] content = memoryStream.ToArray(); System.IO.File.WriteAllBytes(filepath, content); return(fileUrl); }
// #region 根据批次和书号导出excel public void ExportExcel(int batchNum, long bookid) { // int rowNum = 0; string saveFileName = batchNum.ToString() + "_" + bookid.ToString(); string xlsSheetName = ""; /// using (chefuzi_dataEntities myOperating = new chefuzi_dataEntities())//数据库操作 { #region 要开通那本图书 Child_Book_Study myChild_Book_Study = new Child_Book_Study(); myChild_Book_Study = myOperating.Child_Book_Study.FirstOrDefault(p => p.BookID == bookid); if (myChild_Book_Study != null) { xlsSheetName = myChild_Book_Study.BookName; saveFileName = myChild_Book_Study.BookName; } #endregion // if (!String.IsNullOrWhiteSpace(saveFileName)) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(xlsSheetName); // 添加数据 IQueryable <Sys_BookStudy_QRCode> myDataTable = myOperating.Sys_BookStudy_QRCode.Where(p => p.BatchNum == batchNum && p.BookStudyID == bookid); NPOI.SS.UserModel.IRow row = null; foreach (Sys_BookStudy_QRCode item in myDataTable) { row = sheet.CreateRow(rowNum); NPOI.SS.UserModel.ICell cell = row.CreateCell(0); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(StaticVarClass.QRCodeUrl + "1/" + item.QRCode); sheet.SetColumnWidth(0, 20000); rowNum++; } #region 导出文件 if (rowNum > 0) { // 写入 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); book = null; // int updateCount = myOperating.Sys_BookStudy_QRCode.Where(p => p.BatchNum == batchNum && p.BookStudyID == bookid && p.PrintStatus == false).Update(q => new Sys_BookStudy_QRCode() { PrintStatus = true }); if (updateCount > 0) { EFCachClear.ClearTable("Sys_BookStudy_QRCode"); } // Response.ClearHeaders(); Response.Clear(); Response.Expires = 0; Response.Buffer = true; Response.AddHeader("Accept-Language", "zh-tw"); // saveFileName = saveFileName + "_" + batchNum.ToString() + "_" + rowNum + ".xls"; // Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName, System.Text.Encoding.UTF8)); Response.ContentType = "application/octet-stream;charset=gbk"; Response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); Response.End(); } else { Response.AddHeader("Accept-Language", "zh-tw"); Response.Write("没有任何数据导出,请选择图书和批次。"); Response.End(); } #endregion } } }