public void SaveFile() { using (FileStream stream = File.OpenWrite(this.Path)) { _workbook.Write(stream); } _sheet.Dispose(); _workbook.Dispose(); }
/* * 响应到客户端 * * Param fileName 文件名 */ public static void WriteToClient(String fileName, HSSFWorkbook workbook) { //Write the stream data of workbook to the root directory //FileStream file = new FileStream(@"c:/test.xls", FileMode.Create); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.Expires = 0; HttpContext.Current.Response.CacheControl = "no-cache"; HttpContext.Current.Response.ContentType = "application/x-excel"; //inline HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + UTF_FileName(fileName)); HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; workbook.Write(HttpContext.Current.Response.OutputStream); Sheet sheet = workbook.GetSheetAt(0); sheet = null; workbook.Dispose(); workbook = null; HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); //file.Close(); }
/// <summary> /// 将DataTable转成Xls文件导出 /// </summary> /// <param name="page"></param> /// <param name="dt"></param> /// <param name="FileName"></param> /// <returns></returns> public static Stream ExportExcelByNPOI(DataTable dt) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //设定文件相关信息 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Data"; hssfworkbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "Data"; hssfworkbook.SummaryInformation = si; //将DataTable数据写入 Sheet sheet1 = hssfworkbook.CreateSheet("DataExport_NPOI"); for (int i = 0; i < dt.Rows.Count; i++) { //创建行 Row row = sheet1.CreateRow(i); for (int j = 0; j < dt.Columns.Count; j++) { //创建单元格 row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); hssfworkbook.Dispose(); return(file); }
public static DataTable Import(Stream ExcelFileStream, string SheetName, int HeaderRowIndex) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); Sheet sheet = workbook.GetSheet(SheetName); DataTable table = new DataTable(); Row headerRow = sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) { Row row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { dataRow[j] = row.GetCell(j).ToString(); } } ExcelFileStream.Close(); workbook.Dispose(); return(table); }
/* * 生成文件 * * Param fileName 文件名 * * Return 生成文件的URL */ public static string WriteToFile(String fileName, HSSFWorkbook workbook) { //临时文件路径 string tempFilePath = HttpContext.Current.Server.MapPath("~/" + BusinessConstants.TEMP_FILE_PATH); string tempFileName = GetRandomFileName(fileName); if (!Directory.Exists(tempFilePath)) { Directory.CreateDirectory(tempFilePath); } //Write the stream data of workbook to the root directory FileStream file = new FileStream(tempFilePath + tempFileName, FileMode.Create); workbook.Write(file); file.Flush(); file.Close(); file.Dispose(); file = null; Sheet sheet = workbook.GetSheetAt(0); sheet = null; workbook.Dispose(); workbook = null; return(GetShowFileUrl(tempFileName)); }
/// <summary> /// NPOI简单Demo,快速入门代码 /// </summary> /// <param name="dtSource"></param> /// <param name="strFileName"></param> /// <remarks>NPOI认为Excel的第一个单元格是:(0,0)</remarks> /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author> public static void ExportEasy(DataTable dtSource, string strFileName) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); //填充表头 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); foreach (DataColumn column in dtSource.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } //填充内容 for (int i = 0; i < dtSource.Rows.Count; i++) { dataRow = (HSSFRow)sheet.CreateRow(i + 1); for (int j = 0; j < dtSource.Columns.Count; j++) { dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString()); } } //保存 using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } workbook.Dispose(); }
protected virtual void Dispose(bool disposing) { if (!disposed) { Workbook.Dispose(); disposed = true; } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="headerTextDic">头文本</param> /// <param name="fileName">文件名</param> /// <param name="isShowTitle">是否显示表头</param> /// <returns></returns> private static MemoryStream Export(DataTable dtSource, Dictionary <string, string> headerTextDic, string fileName, bool isShowTitle) { var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet() as HSSFSheet; SetSummaryInformation(workbook); var dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; var format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); var arrColWidth = GetColWidth(dtSource, headerTextDic); var rowIndex = 0; var colHeaderStyle = GetColHeaderStyle(workbook); var contentDataStyle = GetContentDataStyle(workbook); foreach (DataRow row in dtSource.Rows) { if (rowIndex == 65536 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } SetDataTitle(headerTextDic, fileName, isShowTitle, workbook, sheet); SetColHeader(dtSource, headerTextDic, isShowTitle, workbook, sheet, arrColWidth, colHeaderStyle); if (isShowTitle) { rowIndex = 2; } else { rowIndex = 1; } } var dataRow = sheet.CreateRow(rowIndex) as HSSFRow; SetContentData(dtSource, headerTextDic, dateStyle, row, dataRow, workbook, contentDataStyle); rowIndex++; } using (var ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); } }
public void TestNPOIBug6341() { using (HSSFWorkbook workbook = OpenSample("Simple.xls")) { int i = workbook.ActiveSheetIndex; } using (HSSFWorkbook workbook = OpenSample("blankworkbook.xls")) { int i = workbook.ActiveSheetIndex; } HSSFWorkbook workbook2 = OpenSample("blankworkbook.xls"); workbook2.Dispose(); NPOI.SS.UserModel.Sheet sheet = workbook2.GetSheetAt(1); }
protected virtual void CreateExcel(DateTime?from, DateTime?to) { //总账科目汇总 List <SAPInterfaceExchangeInfo> lstAll = m_ReconReportDA.CreateExcel(from, to); //总账科目差异 List <SAPInterfaceExchangeInfo> lstUnbalance = GetUnbalance(lstAll); DateTime now = DateTime.Now; string dateStr = to.Value.Date.ToShortDateString().Replace("/", "-"); string beginDateStr = from.Value.ToShortDateString().Replace("-", "/"); string endDateStr = to.Value.Date.ToShortDateString().Replace("-", "/"); string fileName = ReconReportConfig.BasicDirectory + "SAPInterface_" + dateStr + ".xls"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ReconReportExportData reporter = new ReconReportExportData(); reporter.begin = beginDateStr; reporter.end = endDateStr; Sheet sheet1 = hssfworkbook.CreateSheet("SAPInterface_" + dateStr + "(S)"); reporter.ImportDataToSheet(sheet1, lstAll); //统驭科目(AR/AP)汇总 List <SAPInterfaceExchangeInfo> lstOther = m_ReconReportDA.CreateOtherExcel(from, to); //统驭账科目差异 List <SAPInterfaceExchangeInfo> lstOtherUnbalance = GetUnbalance(lstOther); ReconOtherReportExportData OtherReporter = new ReconOtherReportExportData(); OtherReporter.begin = beginDateStr; OtherReporter.end = endDateStr; Sheet sheet2 = hssfworkbook.CreateSheet("AR&AP Report_" + dateStr); OtherReporter.ImportDataToSheet(sheet2, lstOther); FileStream file = new FileStream(fileName, FileMode.Create); hssfworkbook.Write(file); file.Close(); hssfworkbook.Dispose(); //上传 string ftpurl = UpLoad(fileName); //发邮件 SendEmail(ftpurl, dateStr, lstUnbalance, lstOtherUnbalance); }
public void Create(string sheetName, string[][] content, string fullPath) { try { book = new HSSFWorkbook(); sheet = book.CreateSheet(sheetName) as HSSFSheet; for (int i = 0; i < content.Count(); i++) { if (content[i].Any()) { AddTitle(sheet, i, content[i][0]); } if (content[i].Count() > 1) { AddColumnContent(sheet, i, content[i].Skip(1).ToList <string>()); } } file = new FileStream(fullPath, FileMode.OpenOrCreate); book.Write(file); } catch (Exception e) { logger.Error(e.Message, e); throw; } finally { if (file != null) { file.Flush(); file.Close(); } if (sheet != null) { sheet.Dispose(); } if (book != null) { book.Dispose(); } } }
/// <summary> /// NPOI简单Demo,快速入门代码 /// </summary> /// <param name="dtSource"></param> /// <param name="strFileName"></param> /// <remarks>NPOI认为Excel的第一个单元格是:(0,0)</remarks> /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author> public static void ExportEasy(DataTable dtSource, string strFileName) { HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.CreateSheet(); //填充表头 Row dataRow = sheet.CreateRow(0); foreach (DataColumn column in dtSource.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } //填充内容 for (int i = 0; i < dtSource.Rows.Count; i++) { dataRow = sheet.CreateRow(i + 1); for (int j = 0; j < dtSource.Columns.Count; j++) { dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString()); } } //保存 using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(ms); ms.Flush(); ms.Position = 0; byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } workbook.Dispose(); }
public string WriteExcel(List <CustomerInfo> list) { string reportFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Reports"); if (!Directory.Exists(reportFolder)) { Directory.CreateDirectory(reportFolder); } string reportFile = Path.Combine(reportFolder, string.Format("CustomerReportInfo-{0:yyyyMMddHHmmss}.xls", DateTime.Now)); List <List <CustomerInfo> > lists = new List <List <CustomerInfo> >(); for (int k = 0; k < list.Count; k++) { if (k % 50000 == 0) { lists.Add(new List <CustomerInfo>()); } lists[lists.Count - 1].Add(list[k]); } for (int i = 0; i < lists.Count; i++) { Sheet sheet = hssWorkBook.CreateSheet("CustomerInfo" + i.ToString()); SetHead(sheet); SetCoutent(lists[i], sheet); } using (FileStream stream = new FileStream(reportFile, FileMode.Create)) { hssWorkBook.Write(stream); } hssWorkBook.Dispose(); return(reportFile); }
/// <summary> /// 向页面输出excel文件 /// 添加人:冯建 /// 添加时间:2012-12-26 /// </summary> /// <param name="hssfworkbook"></param> /// <param name="excelName">Excel的名称</param> private void WriteToFile(HSSFWorkbook hssfworkbook, string excelName) { var rootPath = Server.MapPath("~/UploadFiles/"); var _title = "/" + excelName + ".xls"; if (!string.IsNullOrEmpty(Request.Browser.Browser)) { if (Request.Browser.Browser.ToLower().IndexOf("ie") >= 0) { _title = Server.UrlEncode(_title); } } //Write the stream data of workbook to the root directory var fs = new FileStream(rootPath + "/" + excelName + ".xls", FileMode.Create, FileAccess.ReadWrite); hssfworkbook.Write(fs); hssfworkbook.Dispose(); fs.Close(); //把文件以流方式指定xls格式提供下载 fs = System.IO.File.OpenRead(rootPath + "/" + excelName + ".xls"); var FileArray = new byte[fs.Length]; fs.Read(FileArray, 0, FileArray.Length); fs.Close(); Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + _title); Response.ContentEncoding = Encoding.UTF8; Response.ContentType = "application/ms-excel"; Response.AddHeader("Content-Length", FileArray.Length.ToString()); Response.BinaryWrite(FileArray); Response.Flush(); Response.End(); Response.Clear(); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = this.Author; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = workbook.CreateCellStyle(); HSSFDataFormat format = workbook.CreateDataFormat(); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(0); HSSFCellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } headerRow.Dispose(); } #endregion rowIndex = 1; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(string.Format("{0:yyyy-MM-dd}", dateV)); break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); } }
/// <summary> /// ListToMemoryStream /// </summary> /// <param name="list">数据源</param> /// <param name="nameList">列头信息</param> public MemoryStream Export <T>(BindingList <T> list, string strHeaderText, List <ExcelHeader> hearderList) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(strHeaderText); #region 文件属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = this.Author; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = Author; si.ApplicationName = ApplicationName; si.Comments = Comments; si.Title = strHeaderText; si.Subject = strHeaderText; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(0); HSSFCellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 600; headStyle.SetFont(font); headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; headStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND; headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index; headStyle.BorderBottom = CellBorderType.THIN; headStyle.BorderLeft = CellBorderType.THIN; headStyle.BorderRight = CellBorderType.THIN; headStyle.BorderTop = CellBorderType.THIN; for (int i = 0; i < hearderList.Count; i++) { headerRow.CreateCell(i).SetCellValue(hearderList[i].HeaderText); headerRow.GetCell(i).CellStyle = headStyle; sheet.SetColumnWidth(i, hearderList[i].Width * 40);//设置列宽 } headerRow.Dispose(); } #endregion int rowIndex = 1; HSSFRow dataRow; HSSFCellStyle style = workbook.CreateCellStyle(); style.BorderBottom = CellBorderType.THIN; style.BorderLeft = CellBorderType.THIN; style.BorderRight = CellBorderType.THIN; style.BorderTop = CellBorderType.THIN; foreach (Object obj in list) { dataRow = sheet.CreateRow(rowIndex); PropertyInfo[] pis = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//就是这个对象所有属性的集合 if (pis != null) { for (int j = 0; j < hearderList.Count; j++) { HSSFCell newCell = dataRow.CreateCell(j); ExcelHeader eh = hearderList[j]; newCell.CellStyle = style; foreach (PropertyInfo pi in pis)//针对每一个属性进行循环 { if (eh.PropertyName.Equals(pi.Name)) { object PropertyValue = pi.GetValue(obj, null); if (PropertyValue != null) { string drValue = PropertyValue.ToString(); switch (PropertyValue.GetType().ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); if (eh.StringFormat != null) { newCell.SetCellValue(string.Format(eh.StringFormat, dateV)); } else { newCell.SetCellValue(string.Format("{0:yyyy-MM-dd}", dateV)); } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } } } } } rowIndex++; } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <returns></returns> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(); HSSFCellStyle dateStyle = workbook.CreateCellStyle(); HSSFDataFormat format = workbook.CreateDataFormat(); //dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; HSSFCellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = CellHorizontalAlignment.CENTER; headStyle.BorderBottom = CellBorderType.THIN; headStyle.BorderLeft = CellBorderType.THIN; headStyle.BorderRight = CellBorderType.THIN; headStyle.BorderTop = CellBorderType.THIN; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { if (strHeaderText != "") { HSSFRow headerRow = sheet.CreateRow(rowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyles = workbook.CreateCellStyle(); headStyles.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 16; font.Boldweight = 700; headStyles.SetFont(font); headStyles.Alignment = CellHorizontalAlignment.CENTER; headStyles.BorderBottom = CellBorderType.THIN; headStyles.BorderLeft = CellBorderType.THIN; headStyles.BorderRight = CellBorderType.THIN; headStyles.BorderTop = CellBorderType.THIN; headerRow.GetCell(0).CellStyle = headStyles; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); headerRow.Dispose(); rowIndex++; } } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } headerRow.Dispose(); } #endregion rowIndex++; } #endregion #region 填充内容 foreach (DataColumn column in dtSource.Columns) { HSSFRow dataRow = sheet.CreateRow(rowIndex); HSSFCell newCell = dataRow.CreateCell(column.Ordinal); newCell.CellStyle = headStyle; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); } }
/// <summary> /// 将单个数据表保存到Excel /// </summary> /// <param name="dataTable">数据表</param> /// <param name="fileName">文件名</param> public static void ExportExcel(DataTable dataTable, string fileName = null) { HSSFWorkbook workBook = new HSSFWorkbook(); Sheet sheet = workBook.CreateSheet("Sheet1"); Font font = workBook.CreateFont(); font.Boldweight = 700; CellStyle style = workBook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font); int rownum = 0; Row row = sheet.CreateRow(rownum++); Cell cell; for (int i = 0; i < dataTable.Columns.Count; i++) { cell = row.CreateCell(i); cell.SetCellValue(dataTable.Columns[i].ColumnName); cell.CellStyle = style; } CellStyle dateStyle = workBook.CreateCellStyle(); DataFormat format = workBook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); foreach (DataRow dataRow in dataTable.Rows) { row = sheet.CreateRow(rownum++); for (int i = 0; i < dataTable.Columns.Count; i++) { cell = row.CreateCell(i); string strValue = dataRow[i].ToString(); if (string.IsNullOrWhiteSpace(strValue)) { cell.SetCellValue(""); } else { switch (dataTable.Columns[i].DataType.ToString()) { case "System.DateTime": DateTime dateTime; DateTime.TryParse(strValue, out dateTime); cell.SetCellValue(dateTime); cell.CellStyle = dateStyle; break; case "System.Boolean": bool bValue; bool.TryParse(strValue, out bValue); cell.SetCellValue(bValue); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": int iValue = 0; int.TryParse(strValue, out iValue); cell.SetCellValue(iValue); break; case "System.Decimal": case "System.Double": double dValue = 0; double.TryParse(strValue, out dValue); cell.SetCellValue(dValue); break; default: cell.SetCellValue(strValue); break; } } } } for (int i = 0; i < dataTable.Columns.Count; i++) { sheet.AutoSizeColumn(i); } workBook.Write(HttpContext.Current.Response.OutputStream); workBook.Dispose(); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; if (string.IsNullOrEmpty(fileName)) { fileName = DateTime.Now.ToString("yyyyMMddHHmmss"); } HttpContext.Current.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" + GetToExcelName(fileName) + ".xls"); HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); }
public void Dispose() { _sheet.Dispose(); _workbook.Dispose(); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "HJCCL"; //填加xls文件作者信息 si.ApplicationName = "使用NPOI封装类"; //填加xls文件创建程序信息 si.LastAuthor = "努力做一个好人"; //填加xls文件最后保存者信息 si.Comments = "交流学习,多多指教"; //填加xls文件作者信息 si.Title = strHeaderText; //填加xls文件标题信息 si.Subject = "HJCCL.Utils.Npoi扩展封装"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { var headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); var headStyle = workbook.CreateCellStyle(); var font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; } #endregion #region 列头及样式 { var headerRow = sheet.CreateRow(1); var headStyle = workbook.CreateCellStyle(); var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = 2; } #endregion #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
// Token: 0x06000042 RID: 66 RVA: 0x000064B8 File Offset: 0x000046B8 protected override void View() { this.examinfo = DbHelper.ExecuteModel <ExamInfo>(this.examid); if (this.examinfo.id == 0) { this.ShowErr("对不起,该试卷不存在或已被删除。"); } else { this.sortid = this.examinfo.sortid; this.sortinfo = SortBll.GetSortInfo(this.sortid); if (this.ispost) { if (this.action == "delete") { string @string = FPRequest.GetString("chkid"); if (DbHelper.ExecuteDelete <ExamResult>(@string) > 0) { SqlParam sqlParam = DbHelper.MakeAndWhere("resultid", WhereType.In, @string); DbHelper.ExecuteDelete <ExamResultTopic>(new SqlParam[] { sqlParam }); } } } if (this.examinfo.examdeparts == "" && this.examinfo.examuser == "" && this.examinfo.examroles == "") { List <SqlParam> list = new List <SqlParam>(); list.Add(DbHelper.MakeAndWhere("examid", this.examid)); if (this.keyword != "") { string text = "0"; SqlParam sqlParam2 = DbHelper.MakeAndWhere(string.Format("([username] LIKE '%{0}%' OR [realname] LIKE '%{0}%')", this.keyword), WhereType.Custom, ""); List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[] { sqlParam2 }); foreach (UserInfo userInfo in list2) { if (text != "") { text += ","; } text += userInfo.id; } list.Add(DbHelper.MakeAndWhere("uid", WhereType.In, text)); } if (this.action == "export") { OrderByParam[] orderbys = new OrderByParam[] { DbHelper.MakeOrderBy("score", OrderBy.DESC), DbHelper.MakeOrderBy("id", OrderBy.ASC) }; this.examresultlist = DbHelper.ExecuteList <ExamResult>(orderbys, list.ToArray()); } else { this.examresultlist = DbHelper.ExecuteList <ExamResult>(this.pager, list.ToArray()); } } else { string text = ""; if (this.examinfo.examroles != "") { SqlParam sqlParam2 = DbHelper.MakeAndWhere("roleid", WhereType.In, this.examinfo.examroles); List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[] { sqlParam2 }); foreach (UserInfo userInfo in list2) { if (!FPUtils.InArray(userInfo.id, text)) { ExamResult examResult = new ExamResult(); examResult.uid = userInfo.id; examResult.examid = this.examid; examResult.status = -1; this.examresultlist.Add(examResult); if (text != "") { text += ","; } text += userInfo.id; } } } if (this.examinfo.examdeparts != "") { SqlParam sqlParam2 = DbHelper.MakeAndWhere("departid", WhereType.In, this.examinfo.examdeparts); List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[] { sqlParam2 }); foreach (UserInfo userInfo in list2) { if (!FPUtils.InArray(userInfo.id, text)) { ExamResult examResult = new ExamResult(); examResult.uid = userInfo.id; examResult.examid = this.examid; examResult.status = -1; this.examresultlist.Add(examResult); if (text != "") { text += ","; } text += userInfo.id; } } } if (this.examinfo.examuser != "") { SqlParam sqlParam2 = DbHelper.MakeAndWhere("id", WhereType.In, this.examinfo.examuser); List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[] { sqlParam2 }); foreach (UserInfo userInfo in list2) { if (!FPUtils.InArray(userInfo.id, text)) { ExamResult examResult = new ExamResult(); examResult.uid = userInfo.id; examResult.examid = this.examid; examResult.status = -1; this.examresultlist.Add(examResult); if (text != "") { text += ","; } text += userInfo.id; } } } SqlParam sqlParam3 = DbHelper.MakeAndWhere("examid", this.examid); OrderByParam orderby = DbHelper.MakeOrderBy("id", OrderBy.ASC); List <ExamResult> list3 = DbHelper.ExecuteList <ExamResult>(orderby, new SqlParam[] { sqlParam3 }); int num = 0; foreach (ExamResult examResult2 in this.examresultlist) { foreach (ExamResult examResult3 in list3) { if (examResult3.uid == examResult2.uid) { this.examresultlist[num].id = examResult3.id; this.examresultlist[num].score = examResult3.score; this.examresultlist[num].starttime = examResult3.starttime; this.examresultlist[num].examdatetime = examResult3.examdatetime; this.examresultlist[num].utime = examResult3.utime; this.examresultlist[num].status = examResult3.status; this.examresultlist[num].questions++; this.examresultlist[num].ip = examResult3.ip; } } num++; } if (this.keyword != "") { list3 = new List <ExamResult>(); foreach (ExamResult examResult2 in this.examresultlist) { if (examResult2.IUser.username.Contains(this.keyword) || examResult2.IUser.realname.Contains(this.keyword)) { list3.Add(examResult2); } } this.examresultlist = new List <ExamResult>(); foreach (ExamResult examResult2 in list3) { this.examresultlist.Add(examResult2); } } if (this.action != "export" && this.action != "report") { this.pager.total = this.examresultlist.Count; int num2 = (this.pager.pageindex - 1) * this.pager.pagesize; int count = this.pager.pagesize; if (num2 + this.pager.pagesize > this.pager.total) { count = this.pager.total - num2; } this.examresultlist = this.examresultlist.GetRange(num2, count); } } if (this.ispost) { if (this.action == "export") { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet hssfsheet = hssfworkbook.CreateSheet("Sheet1"); HSSFCellStyle hssfcellStyle = hssfworkbook.CreateCellStyle(); hssfcellStyle.Alignment = CellHorizontalAlignment.CENTER; hssfcellStyle.VerticalAlignment = CellVerticalAlignment.CENTER; hssfcellStyle.BorderTop = CellBorderType.THIN; hssfcellStyle.BorderRight = CellBorderType.THIN; hssfcellStyle.BorderLeft = CellBorderType.THIN; hssfcellStyle.BorderBottom = CellBorderType.THIN; hssfcellStyle.DataFormat = 0; HSSFFont hssffont = hssfworkbook.CreateFont(); hssffont.Boldweight = short.MaxValue; hssfcellStyle.SetFont(hssffont); HSSFRow hssfrow = hssfsheet.CreateRow(0); hssfrow.CreateCell(0).SetCellValue("用户名"); hssfrow.CreateCell(1).SetCellValue("姓名"); hssfrow.CreateCell(2).SetCellValue("所在部门"); hssfrow.CreateCell(3).SetCellValue("考试得分"); hssfrow.CreateCell(4).SetCellValue("开始时间"); hssfrow.CreateCell(5).SetCellValue("考试用时"); hssfrow.CreateCell(6).SetCellValue("考试状态"); hssfrow.CreateCell(7).SetCellValue(""); hssfrow.Height = 400; hssfsheet.SetColumnWidth(2, 6000); hssfsheet.SetColumnWidth(4, 6000); for (int i = 0; i < 7; i++) { hssfrow.Cells[i].CellStyle = hssfcellStyle; } HSSFCellStyle hssfcellStyle2 = hssfworkbook.CreateCellStyle(); hssfcellStyle2.Alignment = CellHorizontalAlignment.CENTER; hssfcellStyle2.VerticalAlignment = CellVerticalAlignment.CENTER; hssfcellStyle2.BorderTop = CellBorderType.THIN; hssfcellStyle2.BorderRight = CellBorderType.THIN; hssfcellStyle2.BorderLeft = CellBorderType.THIN; hssfcellStyle2.BorderBottom = CellBorderType.THIN; hssfcellStyle2.DataFormat = 0; int num3 = 1; foreach (ExamResult examResult2 in this.examresultlist) { HSSFRow hssfrow2 = hssfsheet.CreateRow(num3); hssfrow2.Height = 300; hssfrow2.CreateCell(0).SetCellValue(examResult2.IUser.username); hssfrow2.CreateCell(1).SetCellValue(examResult2.IUser.realname); hssfrow2.CreateCell(2).SetCellValue(examResult2.IUser.Department.name); hssfrow2.CreateCell(3).SetCellValue(examResult2.score.ToString()); if (examResult2.status >= 0) { hssfrow2.CreateCell(4).SetCellValue(examResult2.examdatetime.ToString("yyyy-MM-dd HH:mm:dd")); hssfrow2.CreateCell(5).SetCellValue((examResult2.utime / 60 + 1).ToString() + "分钟"); } else { hssfrow2.CreateCell(4).SetCellValue(""); hssfrow2.CreateCell(5).SetCellValue(""); } if (examResult2.status == 1) { hssfrow2.CreateCell(6).SetCellValue("已交卷"); } else if (examResult2.status == 2) { hssfrow2.CreateCell(6).SetCellValue("已阅卷"); } else if (examResult2.status == 0) { hssfrow2.CreateCell(6).SetCellValue("未交卷"); } else { hssfrow2.CreateCell(6).SetCellValue("缺考"); } hssfrow2.CreateCell(7).SetCellValue(""); for (int i = 0; i < 7; i++) { hssfrow2.Cells[i].CellStyle = hssfcellStyle2; } num3++; } using (MemoryStream memoryStream = new MemoryStream()) { hssfworkbook.Write(memoryStream); memoryStream.Flush(); memoryStream.Position = 0L; hssfsheet.Dispose(); hssfworkbook.Dispose(); base.Response.ContentType = "application/vnd.ms-excel"; base.Response.ContentEncoding = Encoding.UTF8; base.Response.Charset = ""; base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(this.examinfo.name + "成绩表.xls")); base.Response.BinaryWrite(memoryStream.GetBuffer()); base.Response.Flush(); base.Response.End(); } } else if (this.action == "report") { AsposeWordApp asposeWordApp = new AsposeWordApp(); asposeWordApp.Open(FPUtils.GetMapPath("images\\examreport.doc")); asposeWordApp.InsertText("examtitle", this.examinfo.name); asposeWordApp.InsertText("username", this.user.realname); asposeWordApp.InsertText("total", this.examinfo.total.ToString() + "分"); if (this.examinfo.islimit == 1) { asposeWordApp.InsertText("examtime", this.examinfo.starttime.ToString("yyyy-MM-dd HH:mm")); } else { asposeWordApp.InsertText("examtime", "不限制"); } asposeWordApp.InsertText("exampass", (this.examinfo.passmark * this.examinfo.total / 100.0).ToString() + "分"); asposeWordApp.InsertText("qtime", this.examinfo.examtime.ToString() + "分钟"); asposeWordApp.InsertText("examuser", this.examinfo.exams.ToString() + "人"); if (this.examinfo.exams > 0) { asposeWordApp.InsertText("examavg", (this.examinfo.score / (double)this.examinfo.exams).ToString("0.0")); } else { asposeWordApp.InsertText("examavg", "0"); } int[] array = new int[5]; foreach (ExamResult examResult2 in this.examresultlist) { if (examResult2.score < 60.0) { array[0]++; } else if (examResult2.score >= 60.0 && examResult2.score < 70.0) { array[1]++; } else if (examResult2.score >= 70.0 && examResult2.score < 80.0) { array[2]++; } else if (examResult2.score >= 80.0 && examResult2.score < 90.0) { array[3]++; } else if (examResult2.score >= 90.0) { array[4]++; } } int i = 1; foreach (int num4 in array) { asposeWordApp.InsertText("s" + i, num4.ToString() + "人"); asposeWordApp.InsertText("p" + i, (num4 / this.examinfo.exams * 100).ToString("0.0") + "%"); i++; } asposeWordApp.Save(base.Response, this.examinfo.name + "_考试分析报告.doc"); } } base.SaveRightURL(); } }
private void Import_To_Grid(string FilePath, string Extension, string isHDR) { if (Extension == ".xls") { //string notxlsx = ("This is not an xlsx file"); //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read)) hssfworkbook = new HSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); //Counts the number of cells in a row and determines the columns from that. int counter = sheet.GetRow(0).Cells.Count; // J < number of columns needs to be exact at this moment for (int j = 0; j < counter; j++) { // set each column to a - ** letters // dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); //Get first row and set the headers for each cell //dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString()); //Get each cell value in row 0 and return its string for a column name. dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue); } while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } //Hackish way to remove the bad first row made by getting column names dt.Rows.RemoveAt(0); GridView1.Caption = Path.GetFileName(FilePath); GridView1.DataSource = dt; //Bind the data GridView1.DataBind(); sheet.Dispose(); hssfworkbook.Dispose(); } else { //Create a new epplus package using openxml var pck = new OfficeOpenXml.ExcelPackage(); //load the package with the filepath I got from my fileuploader above on the button //pck.Load(new System.IO.FileInfo(FilePath).OpenRead()); //stream the package FileStream stream = new FileStream(FilePath, FileMode.Open); pck.Load(stream); //So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause. //Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all. var ws = pck.Workbook.Worksheets[1]; //This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"]; if (ws == null) { ws = pck.Workbook.Worksheets.Add("Sheet1"); // Obiviously I didn't add anything to the sheet so probably can count on it being blank. } //I created this datatable for below. DataTable tbl = new DataTable(); //My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx var hdr = bool.Parse(isHDR); Console.WriteLine(hdr); //Set the bool value for from above. var hasHeader = hdr; //Setup the table based on the value from my bool foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; var row = tbl.NewRow(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } tbl.Rows.Add(row); } //Bind Data to GridView //I have all my info in the tbl dataTable so the datasource for the Gridview1 is set to tbl GridView1.Caption = Path.GetFileName(FilePath); GridView1.DataSource = tbl; //Bind the data GridView1.DataBind(); pck.Save(); pck.Dispose(); stream.Close(); // string pathD = FilePath; FilePath = null; stream = null; // var fileToDelete = new FileInfo(pathD); // fileToDelete.Delete(); } }
private static MemoryStream Export2Delivery(DeliveryNote note) { HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "ChuTian"; //填加xls文件作者信息 si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息 si.LastAuthor = "ChuTian"; //填加xls文件最后保存者信息 si.Comments = ""; //填加xls文件作者信息 si.Title = ""; //填加xls文件标题信息 si.Subject = ""; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion //CellStyle dateStyle = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 1; sheet.SetColumnWidth(0, (int)((9.5 + 0.72) * 256)); sheet.SetColumnWidth(1, (int)((18.5 + 0.72) * 256)); sheet.SetColumnWidth(2, (int)((9.5 + 0.72) * 256)); sheet.SetColumnWidth(3, (int)((12.5 + 0.72) * 256)); sheet.SetColumnWidth(4, (int)((12.5 + 0.72) * 256)); sheet.SetColumnWidth(5, (int)((15.5 + 0.72) * 256)); sheet.SetColumnWidth(6, (int)((20 + 0.72) * 256)); //所有的字体 Font font18 = workbook.CreateFont(); font18.FontName = "华文彩云"; font18.FontHeightInPoints = 18; font18.IsItalic = true; Font font11Bold = workbook.CreateFont(); font11Bold.FontHeightInPoints = 11; font11Bold.Boldweight = 700; Font font11Normal = workbook.CreateFont(); font11Normal.FontHeightInPoints = 11; Font font20Bold = workbook.CreateFont(); font20Bold.FontHeightInPoints = 20; font20Bold.Boldweight = 700; Font foot10Bold = workbook.CreateFont(); foot10Bold.FontHeightInPoints = 10; foot10Bold.Boldweight = 700; CellStyle companyStyle = workbook.CreateCellStyle(); companyStyle.Alignment = HorizontalAlignment.CENTER; companyStyle.SetFont(font18); Row companyRow = sheet.CreateRow(rowIndex); companyRow.HeightInPoints = 30; companyRow.CreateCell(0).SetCellValue(" 湖北楚天通讯材料有限公司"); companyRow.GetCell(0).CellStyle = companyStyle; rowIndex++; //创建表头 CellStyle titleStyle = workbook.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.CENTER; titleStyle.SetFont(font20Bold); Row titleRow = sheet.CreateRow(rowIndex); titleRow.HeightInPoints = 29; titleRow.CreateCell(0).SetCellValue(" 送 货 单"); titleRow.GetCell(0).CellStyle = titleStyle; //创建Logo string strLogoPath = AppDomain.CurrentDomain.BaseDirectory + "ctlogo.png"; byte[] bLogo = System.IO.File.ReadAllBytes(strLogoPath); int pictureIdx = workbook.AddPicture(bLogo, PictureType.PNG); var patriarch = sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 82, 39, 0, 2, 0, 2); var pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); rowIndex++; rowIndex++; //创建父表内容 CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.LEFT; headStyle.SetFont(font11Bold); CellStyle headDescStyle = workbook.CreateCellStyle(); headDescStyle.Alignment = HorizontalAlignment.RIGHT; headDescStyle.SetFont(font11Normal); Row parentRow1 = sheet.CreateRow(rowIndex); parentRow1.CreateCell(0).SetCellValue("客户:"); parentRow1.CreateCell(1).SetCellValue(note.customer); parentRow1.CreateCell(4).SetCellValue("送货单号:"); parentRow1.CreateCell(5).SetCellValue(note.deliverid.ToString().PadLeft(3, '0')); parentRow1.CreateCell(6).SetCellValue(note.description); parentRow1.GetCell(0).CellStyle = headStyle; parentRow1.GetCell(1).CellStyle = headStyle; parentRow1.GetCell(4).CellStyle = headStyle; parentRow1.GetCell(5).CellStyle = headStyle; parentRow1.GetCell(6).CellStyle = headDescStyle; rowIndex++; Row parentRow2 = sheet.CreateRow(rowIndex); parentRow2.CreateCell(0).SetCellValue("型号:"); parentRow2.CreateCell(1).SetCellValue(note.model); parentRow2.CreateCell(4).SetCellValue("发货时间:"); parentRow2.CreateCell(5).SetCellValue(note.deliverdate.ToString("yyyy.MM.dd")); parentRow2.GetCell(0).CellStyle = headStyle; parentRow2.GetCell(1).CellStyle = headStyle; parentRow2.GetCell(4).CellStyle = headStyle; parentRow2.GetCell(5).CellStyle = headStyle; rowIndex++; Row parentRow3 = sheet.CreateRow(rowIndex); parentRow3.CreateCell(0).SetCellValue("货物名称:"); parentRow3.CreateCell(1).SetCellValue(note.goodname); parentRow3.CreateCell(4).SetCellValue("出厂批号:"); parentRow3.CreateCell(5).SetCellValue(note.batch); parentRow3.GetCell(0).CellStyle = headStyle; parentRow3.GetCell(1).CellStyle = headStyle; parentRow3.GetCell(4).CellStyle = headStyle; parentRow3.GetCell(5).CellStyle = headStyle; rowIndex++; //表格样式 CellStyle tableStyle = workbook.CreateCellStyle(); CellStyle tableLeftStyle = workbook.CreateCellStyle(); tableStyle.Alignment = HorizontalAlignment.CENTER; tableLeftStyle.Alignment = HorizontalAlignment.LEFT; tableStyle.SetFont(font11Normal); tableLeftStyle.SetFont(font11Normal); tableStyle.BorderTop = CellBorderType.THIN; tableStyle.BorderBottom = CellBorderType.THIN; tableStyle.BorderLeft = CellBorderType.THIN; tableStyle.BorderRight = CellBorderType.THIN; tableLeftStyle.BorderTop = CellBorderType.THIN; tableLeftStyle.BorderBottom = CellBorderType.THIN; tableLeftStyle.BorderLeft = CellBorderType.THIN; tableLeftStyle.BorderRight = CellBorderType.THIN; //小计和合计样式 CellStyle tableSumStyle = workbook.CreateCellStyle(); CellStyle tableSumLeftStyle = workbook.CreateCellStyle(); tableSumStyle.Alignment = HorizontalAlignment.CENTER; tableSumLeftStyle.Alignment = HorizontalAlignment.LEFT; tableSumStyle.SetFont(font11Bold); tableSumLeftStyle.SetFont(font11Bold); tableSumStyle.BorderTop = CellBorderType.THIN; tableSumStyle.BorderBottom = CellBorderType.THIN; tableSumStyle.BorderLeft = CellBorderType.THIN; tableSumStyle.BorderRight = CellBorderType.THIN; tableSumLeftStyle.BorderTop = CellBorderType.THIN; tableSumLeftStyle.BorderBottom = CellBorderType.THIN; tableSumLeftStyle.BorderLeft = CellBorderType.THIN; tableSumLeftStyle.BorderRight = CellBorderType.THIN; Row colHeaderRow = sheet.CreateRow(rowIndex); colHeaderRow.CreateCell(0).SetCellValue("件号"); colHeaderRow.CreateCell(1).SetCellValue("规格"); colHeaderRow.CreateCell(2).SetCellValue("盘数"); colHeaderRow.CreateCell(3).SetCellValue("净重(KG)"); colHeaderRow.CreateCell(4).SetCellValue("单价"); colHeaderRow.CreateCell(5).SetCellValue("金额"); colHeaderRow.CreateCell(6).SetCellValue("合同号"); colHeaderRow.CreateCell(7).SetCellValue("毛重"); colHeaderRow.CreateCell(8).SetCellValue("管芯重量"); colHeaderRow.GetCell(0).CellStyle = tableLeftStyle; colHeaderRow.GetCell(1).CellStyle = tableStyle; colHeaderRow.GetCell(2).CellStyle = tableStyle; colHeaderRow.GetCell(3).CellStyle = tableStyle; colHeaderRow.GetCell(4).CellStyle = tableStyle; colHeaderRow.GetCell(5).CellStyle = tableStyle; colHeaderRow.GetCell(6).CellStyle = tableStyle; colHeaderRow.GetCell(7).CellStyle = tableStyle; colHeaderRow.GetCell(8).CellStyle = tableStyle; rowIndex++; Dictionary <string, List <DeliveryItem> > dicDelivery = DeliveryItemGroup(note.items); int iDisoTotal = 0; double dWeightTotal = 0; double dPriceTotal = 0; foreach (string delivertyId in dicDelivery.Keys) { int iDisoSum = 0; double dWeightSum = 0; double dPriceSum = 0; List <DeliveryItem> noteItems = dicDelivery[delivertyId]; foreach (DeliveryItem item in noteItems) { iDisoSum = iDisoSum + item.discnum; dWeightSum = dWeightSum + item.weight; dPriceSum = dPriceSum + item.totalprice; iDisoTotal = iDisoTotal + item.discnum; dWeightTotal = dWeightTotal + item.weight; dPriceTotal = dPriceTotal + item.totalprice; Row colItemRow = sheet.CreateRow(rowIndex); colItemRow.CreateCell(0).SetCellValue(item.jiannum); colItemRow.GetCell(0).CellStyle = tableLeftStyle; colItemRow.CreateCell(1).SetCellValue(item.specifications + "*" + item.lenght.ToString()); colItemRow.GetCell(1).CellStyle = tableStyle; colItemRow.CreateCell(2).SetCellValue(item.discnum); colItemRow.GetCell(2).CellStyle = tableStyle; colItemRow.CreateCell(3).SetCellValue(item.weight.ToString("f2")); colItemRow.GetCell(3).CellStyle = tableStyle; colItemRow.CreateCell(4).SetCellValue(item.price.ToString("f5")); colItemRow.GetCell(4).CellStyle = tableStyle; colItemRow.CreateCell(5).SetCellValue(item.totalprice.ToString("f5")); colItemRow.GetCell(5).CellStyle = tableStyle; colItemRow.CreateCell(6).SetCellValue(item.contractno); colItemRow.GetCell(6).CellStyle = tableStyle; colItemRow.CreateCell(7).SetCellValue(item.netweight); colItemRow.GetCell(7).CellStyle = tableStyle; colItemRow.CreateCell(8).SetCellValue(item.coreweight); colItemRow.GetCell(8).CellStyle = tableStyle; rowIndex++; } if (noteItems.Count > 1 && dicDelivery.Count > 1) { Row colItemRow = sheet.CreateRow(rowIndex); colItemRow.CreateCell(0).SetCellValue("小计"); colItemRow.GetCell(0).CellStyle = tableSumLeftStyle; colItemRow.CreateCell(1).SetCellValue(""); colItemRow.GetCell(1).CellStyle = tableSumStyle; colItemRow.CreateCell(2).SetCellValue(iDisoSum.ToString()); colItemRow.GetCell(2).CellStyle = tableSumStyle; colItemRow.CreateCell(3).SetCellValue(dWeightSum.ToString("f2")); colItemRow.GetCell(3).CellStyle = tableSumStyle; colItemRow.CreateCell(4).SetCellValue(""); colItemRow.GetCell(4).CellStyle = tableSumStyle; colItemRow.CreateCell(5).SetCellValue(dPriceSum.ToString("f2")); colItemRow.GetCell(5).CellStyle = tableSumStyle; colItemRow.CreateCell(6).SetCellValue(""); colItemRow.GetCell(6).CellStyle = tableSumStyle; colItemRow.CreateCell(7).SetCellValue(""); colItemRow.GetCell(7).CellStyle = tableSumStyle; colItemRow.CreateCell(8).SetCellValue(""); colItemRow.GetCell(8).CellStyle = tableSumStyle; rowIndex++; } else { Row colItemRow = sheet.CreateRow(rowIndex); colItemRow.CreateCell(0).SetCellValue(""); colItemRow.GetCell(0).CellStyle = tableSumLeftStyle; colItemRow.CreateCell(1).SetCellValue(""); colItemRow.GetCell(1).CellStyle = tableSumStyle; colItemRow.CreateCell(2).SetCellValue(""); colItemRow.GetCell(2).CellStyle = tableSumStyle; colItemRow.CreateCell(3).SetCellValue(""); colItemRow.GetCell(3).CellStyle = tableSumStyle; colItemRow.CreateCell(4).SetCellValue(""); colItemRow.GetCell(4).CellStyle = tableSumStyle; colItemRow.CreateCell(5).SetCellValue(""); colItemRow.GetCell(5).CellStyle = tableSumStyle; colItemRow.CreateCell(6).SetCellValue(""); colItemRow.GetCell(6).CellStyle = tableSumStyle; colItemRow.CreateCell(7).SetCellValue(""); colItemRow.GetCell(7).CellStyle = tableSumStyle; colItemRow.CreateCell(8).SetCellValue(""); colItemRow.GetCell(8).CellStyle = tableSumStyle; rowIndex++; } } //空行 Row colBlankRow = sheet.CreateRow(rowIndex); colBlankRow.CreateCell(0).SetCellValue(""); colBlankRow.GetCell(0).CellStyle = tableLeftStyle; colBlankRow.CreateCell(1).SetCellValue(""); colBlankRow.GetCell(1).CellStyle = tableStyle; colBlankRow.CreateCell(2).SetCellValue(""); colBlankRow.GetCell(2).CellStyle = tableStyle; colBlankRow.CreateCell(3).SetCellValue(""); colBlankRow.GetCell(3).CellStyle = tableStyle; colBlankRow.CreateCell(4).SetCellValue(""); colBlankRow.GetCell(4).CellStyle = tableStyle; colBlankRow.CreateCell(5).SetCellValue(""); colBlankRow.GetCell(5).CellStyle = tableStyle; colBlankRow.CreateCell(6).SetCellValue(""); colBlankRow.GetCell(6).CellStyle = tableStyle; colBlankRow.CreateCell(7).SetCellValue(""); colBlankRow.GetCell(7).CellStyle = tableStyle; colBlankRow.CreateCell(8).SetCellValue(""); colBlankRow.GetCell(8).CellStyle = tableStyle; rowIndex++; //合计 Row colTotalRow = sheet.CreateRow(rowIndex); colTotalRow.CreateCell(0).SetCellValue("合计"); colTotalRow.GetCell(0).CellStyle = tableSumLeftStyle; colTotalRow.CreateCell(1).SetCellValue(""); colTotalRow.GetCell(1).CellStyle = tableSumStyle; colTotalRow.CreateCell(2).SetCellValue(iDisoTotal.ToString()); colTotalRow.GetCell(2).CellStyle = tableSumStyle; colTotalRow.CreateCell(3).SetCellValue(dWeightTotal.ToString("f2")); colTotalRow.GetCell(3).CellStyle = tableSumStyle; colTotalRow.CreateCell(4).SetCellValue(""); colTotalRow.GetCell(4).CellStyle = tableSumStyle; colTotalRow.CreateCell(5).SetCellValue(dPriceTotal.ToString("f2")); colTotalRow.GetCell(5).CellStyle = tableSumStyle; colTotalRow.CreateCell(6).SetCellValue(""); colTotalRow.GetCell(6).CellStyle = tableSumStyle; colTotalRow.CreateCell(7).SetCellValue(""); colTotalRow.GetCell(7).CellStyle = tableSumStyle; colTotalRow.CreateCell(8).SetCellValue(""); colTotalRow.GetCell(8).CellStyle = tableSumStyle; rowIndex++; CellStyle footStyle = workbook.CreateCellStyle(); footStyle.Alignment = HorizontalAlignment.LEFT; footStyle.SetFont(font11Normal); CellStyle footBoldStyle = workbook.CreateCellStyle(); footBoldStyle.Alignment = HorizontalAlignment.LEFT; footBoldStyle.SetFont(font11Bold); CellStyle footRightStyle = workbook.CreateCellStyle(); footRightStyle.Alignment = HorizontalAlignment.RIGHT; footRightStyle.SetFont(font11Normal); Row descRow = sheet.CreateRow(rowIndex); descRow.CreateCell(0).SetCellValue("备注:"); descRow.GetCell(0).CellStyle = footBoldStyle; descRow.CreateCell(1).SetCellValue(note.description1); descRow.GetCell(1).CellStyle = footBoldStyle; rowIndex++; Row footerRow = sheet.CreateRow(rowIndex); footerRow.CreateCell(0).SetCellValue("请按上列货验收"); footerRow.GetCell(0).CellStyle = footStyle; rowIndex++; Row footerRow1 = sheet.CreateRow(rowIndex); footerRow1.CreateCell(0).SetCellValue("收货人:"); footerRow1.GetCell(0).CellStyle = footStyle; footerRow1.CreateCell(1).SetCellValue(""); footerRow1.GetCell(1).CellStyle = footStyle; footerRow1.CreateCell(2).SetCellValue("送货人:"); footerRow1.GetCell(2).CellStyle = footStyle; footerRow1.CreateCell(3).SetCellValue(""); footerRow1.GetCell(3).CellStyle = footRightStyle; footerRow1.CreateCell(4).SetCellValue("制单:"); footerRow1.GetCell(4).CellStyle = footRightStyle; footerRow1.CreateCell(5).SetCellValue(note.loginid); footerRow1.GetCell(5).CellStyle = footStyle; footerRow1.CreateCell(6).SetCellValue("审核:"); footerRow1.GetCell(6).CellStyle = footStyle; rowIndex++; CellStyle footStyle1 = workbook.CreateCellStyle(); footStyle1.Alignment = HorizontalAlignment.LEFT; footStyle1.SetFont(foot10Bold); Row footerRow2 = sheet.CreateRow(rowIndex); footerRow2.CreateCell(0).SetCellValue("地址:湖北省汉川市马口工业园区楚天路"); footerRow2.GetCell(0).CellStyle = footStyle1; footerRow2.CreateCell(1).SetCellValue(""); footerRow2.GetCell(1).CellStyle = footStyle1; footerRow2.CreateCell(2).SetCellValue(""); footerRow2.GetCell(2).CellStyle = footStyle1; footerRow2.CreateCell(3).SetCellValue(" 电话:0712-8521088 传真:0712-8512311"); footerRow2.GetCell(3).CellStyle = footStyle1; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2)); using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); } }
//private static WriteLog wl = new WriteLog(); #region 从datatable中将数据导出到excel /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> static MemoryStream ExportDT(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; #region 右击文件 属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "柳永法"; //填加xls文件作者信息 // si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息 // si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息 // si.Comments = "说明信息"; //填加xls文件作者信息 // si.Title = "NPOI测试"; //填加xls文件标题信息 // si.Subject = "NPOI测试Demo"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} #endregion HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); } }
// Token: 0x06000076 RID: 118 RVA: 0x0000BE54 File Offset: 0x0000A054 protected override void View() { this.examconfig = ExamConifgs.GetExamConfig(); this.sortinfo = SortBll.GetSortInfo(this.sortid); if (this.sortinfo.id <= 0) { this.ShowErr("该题库已被删除或不存在"); } else { if (this.channelid == 0) { this.channelid = this.sortinfo.channelid; } string childSorts = SortBll.GetChildSorts(this.sortinfo); List <SqlParam> list = new List <SqlParam>(); list.Add(DbHelper.MakeAndWhere("sortid", WhereType.In, childSorts)); if (this.type > 0) { list.Add(DbHelper.MakeAndWhere("type", this.type)); } if (this.keyword != "") { list.Add(DbHelper.MakeAndWhere("title", WhereType.Like, this.keyword)); } if (this.ispost) { if (this.action == "delete") { string @string = FPRequest.GetString("chkid"); string questionSorts = QuestionBll.GetQuestionSorts(@string); SortBll.UpdateSortPosts(questionSorts, -1); DbHelper.ExecuteDelete <ExamQuestion>(@string); } else if (this.action == "clear") { DbHelper.ExecuteDelete <ExamQuestion>(new SqlParam[] { list[0] }); } else if (this.action == "move") { string @string = FPRequest.GetString("chkid"); if (@string == "") { this.ShowErr("对不起,您未选择任何选项"); return; } base.Response.Redirect(string.Concat(new object[] { "questionmove.aspx?channelid=", this.channelid, "&sortid=", this.sortid, "&pageindex=", this.pager.pageindex, "&chkid=", @string })); } else if (this.action == "export") { List <ExamQuestion> list2 = DbHelper.ExecuteList <ExamQuestion>(list.ToArray()); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet hssfsheet = hssfworkbook.CreateSheet("Sheet1"); HSSFCellStyle hssfcellStyle = hssfworkbook.CreateCellStyle(); hssfcellStyle.Alignment = CellHorizontalAlignment.CENTER; hssfcellStyle.VerticalAlignment = CellVerticalAlignment.CENTER; hssfcellStyle.BorderTop = CellBorderType.THIN; hssfcellStyle.BorderRight = CellBorderType.THIN; hssfcellStyle.BorderLeft = CellBorderType.THIN; hssfcellStyle.BorderBottom = CellBorderType.THIN; hssfcellStyle.DataFormat = 0; HSSFFont hssffont = hssfworkbook.CreateFont(); hssffont.Boldweight = short.MaxValue; hssfcellStyle.SetFont(hssffont); HSSFRow hssfrow = hssfsheet.CreateRow(0); hssfrow.CreateCell(0).SetCellValue("题目类型"); hssfrow.CreateCell(1).SetCellValue("题目标题"); hssfrow.CreateCell(2).SetCellValue("选项A"); hssfrow.CreateCell(3).SetCellValue("选项B"); hssfrow.CreateCell(4).SetCellValue("选项C"); hssfrow.CreateCell(5).SetCellValue("选项D"); hssfrow.CreateCell(6).SetCellValue("选项E"); hssfrow.CreateCell(7).SetCellValue("选项F"); hssfrow.CreateCell(8).SetCellValue("正确答案"); hssfrow.CreateCell(9).SetCellValue("答案关键词"); hssfrow.CreateCell(10).SetCellValue("答案解释"); hssfrow.CreateCell(11).SetCellValue("难易程度"); hssfrow.CreateCell(12).SetCellValue("随机题目"); hssfrow.CreateCell(13).SetCellValue("所在题库"); hssfrow.CreateCell(14).SetCellValue(""); hssfrow.Height = 400; hssfsheet.SetColumnWidth(1, 6000); for (int i = 0; i < 14; i++) { hssfrow.Cells[i].CellStyle = hssfcellStyle; } HSSFCellStyle hssfcellStyle2 = hssfworkbook.CreateCellStyle(); hssfcellStyle2.Alignment = CellHorizontalAlignment.CENTER; hssfcellStyle2.VerticalAlignment = CellVerticalAlignment.CENTER; hssfcellStyle2.BorderTop = CellBorderType.THIN; hssfcellStyle2.BorderRight = CellBorderType.THIN; hssfcellStyle2.BorderLeft = CellBorderType.THIN; hssfcellStyle2.BorderBottom = CellBorderType.THIN; hssfcellStyle2.DataFormat = 0; int num = 1; foreach (ExamQuestion examQuestion in list2) { HSSFRow hssfrow2 = hssfsheet.CreateRow(num); hssfrow2.Height = 300; hssfrow2.CreateCell(0).SetCellValue(this.TypeStr(examQuestion.type)); hssfrow2.CreateCell(1).SetCellValue(examQuestion.title.Trim()); if (examQuestion.type == 1 || examQuestion.type == 2) { string[] array = FPUtils.SplitString(examQuestion.content, "§", 6); int num2 = 0; foreach (string cellValue in array) { hssfrow2.CreateCell(2 + num2).SetCellValue(cellValue); num2++; } } else if (examQuestion.type == 4) { if (examQuestion.upperflg == 1) { hssfrow2.CreateCell(2).SetCellValue("区分大小写"); } else { hssfrow2.CreateCell(2).SetCellValue(""); } if (examQuestion.orderflg == 1) { hssfrow2.CreateCell(3).SetCellValue("区分顺序"); } else { hssfrow2.CreateCell(3).SetCellValue(""); } hssfrow2.CreateCell(4).SetCellValue(""); hssfrow2.CreateCell(5).SetCellValue(""); hssfrow2.CreateCell(6).SetCellValue(""); hssfrow2.CreateCell(7).SetCellValue(""); } else { hssfrow2.CreateCell(2).SetCellValue(examQuestion.content.Trim()); hssfrow2.CreateCell(3).SetCellValue(""); hssfrow2.CreateCell(4).SetCellValue(""); hssfrow2.CreateCell(5).SetCellValue(""); hssfrow2.CreateCell(6).SetCellValue(""); hssfrow2.CreateCell(7).SetCellValue(""); } hssfrow2.CreateCell(8).SetCellValue(examQuestion.answer.Trim()); hssfrow2.CreateCell(9).SetCellValue(examQuestion.answerkey.Trim()); hssfrow2.CreateCell(10).SetCellValue(examQuestion.explain.Trim()); hssfrow2.CreateCell(11).SetCellValue(this.DifficultyStr(examQuestion.difficulty)); hssfrow2.CreateCell(12).SetCellValue((examQuestion.status == 1) ? "是" : "否"); hssfrow2.CreateCell(13).SetCellValue(""); hssfrow2.CreateCell(14).SetCellValue(""); for (int i = 0; i < 14; i++) { hssfrow2.Cells[i].CellStyle = hssfcellStyle2; } num++; } using (MemoryStream memoryStream = new MemoryStream()) { hssfworkbook.Write(memoryStream); memoryStream.Flush(); memoryStream.Position = 0L; hssfsheet.Dispose(); hssfworkbook.Dispose(); base.Response.ContentType = "application/vnd.ms-excel"; base.Response.ContentEncoding = Encoding.UTF8; base.Response.Charset = ""; base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(this.sortinfo.name + "题库.xls")); base.Response.BinaryWrite(memoryStream.GetBuffer()); base.Response.Flush(); base.Response.End(); } } } this.questionlist = DbHelper.ExecuteList <ExamQuestion>(this.pager, list.ToArray()); if (this.sortinfo.posts != this.pager.total) { string sqlstring = string.Format("UPDATE [{0}WMS_SortInfo] SET [posts]={1} WHERE [id]={2}", DbConfigs.Prefix, this.pager.total, this.sortid); DbHelper.ExecuteSql(sqlstring); } base.SaveRightURL(); } }