/* * 响应到客户端 * * 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(); }
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(); } }
/* * 生成文件 * * 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); }
private static void WriteXLS(string pathTemplate, List<TempTransfer> listTransfers, Report reportTo) { string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory),"Targetfile" + DateTime.Now.ToString("yyy_MM_dd_hh_mm") + ".xls"); int row = reportTo.ResultRow; HSSFRow currentRow; HSSFCell currentCell; using (FileStream fs = new FileStream(pathTemplate, FileMode.Open, FileAccess.Read)) { //getting complete workbook HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true); // Getting the worksheet HSSFSheet sheet = templateWorkbook.GetSheet(reportTo.SheetName) as HSSFSheet; foreach (TempTransfer item in listTransfers.OrderBy(t => t.RowNumber).ThenBy(t => t.ColIndex)) { currentRow = sheet.GetRow(Convert.ToInt32(reportTo.ResultRow - 1 + item.RowNumber - 1)) as HSSFRow; //check if row exists if (currentRow == null) { currentRow = sheet.CreateRow(Convert.ToInt32(reportTo.ResultRow - 1 + item.RowNumber - 1)) as HSSFRow; } currentCell = currentRow.GetCell(item.ColIndex - 1) as HSSFCell; if (currentCell == null) { currentCell = currentRow.CreateCell(item.ColIndex - 1) as HSSFCell; } //hardcoded for the LIMS Template //need to make it more generic if (!item.ColIndex.Equals(8)) { //Not a UWI currentCell.SetCellValue(item.Value); } else { if (Regex.IsMatch(item.Value.ToUpper(), @"\d{2,3}/\d{2}-\d{2}-\d{3}-\d{2}W\d/\d{2}")) { Dictionary<string, string> uwi = UWI.ParseUWIAlberta(item.Value); currentCell.SetCellValue(uwi["wellIdent"]); currentRow.CreateCell(8).SetCellValue(uwi["wellLegalSub"]); currentRow.CreateCell(9).SetCellValue(uwi["wellSection"]); currentRow.CreateCell(10).SetCellValue(uwi["wellTownShip"]); currentRow.CreateCell(11).SetCellValue(uwi["wellRange"]); currentRow.CreateCell(12).SetCellValue(uwi["wellMeridian"]); } } } using (FileStream writer = new FileStream(path, FileMode.Create, FileAccess.Write)) { templateWorkbook.Write(writer); } sheet.Dispose(); templateWorkbook.Dispose(); } }
//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; } }
/// </summary> /// <param name="dt"> 数据源</param> /// <returns>stream</returns> public static Stream StreamData(DataTable dt, string sheetname, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = null; HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } sheet = (HSSFSheet)workbook.CreateSheet(sheetname); #region 表头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dt.Columns.Count - 1)); headerRow = null; //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dt.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable,将datatable的列名赋予sheet headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } headerRow = null; } #endregion int index = 2; //表头和列头已经占用一行,所以从2开始 foreach (DataRow row in dt.Rows) { HSSFRow datarow = (HSSFRow)sheet.CreateRow(index); foreach (DataColumn column in dt.Columns) { // row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); // 实例化cell string drValue = row[column].ToString(); if (drValue == null || drValue == "") { newCell.SetCellValue(""); continue; } switch (column.DataType.ToString()) { case "System.String"://字符串类型 case "System.DateTime"://日期类型 newCell.SetCellValue(drValue); 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": case "System.Float": case "System.Single": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } index++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; //headerrow = null; //workbook = null; workbook.Dispose(); return ms; }
/// <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 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; }
/// <summary> /// 開始匯出資料 /// </summary> private void Export() { SetCondition(cbRegion, lblRegion, false); SetCondition(cbTechnology, lblTechID, false); string strRegion = ""; foreach(string s in lblRegion.Text.Trim().Split(',')) { if (s != "") { strRegion += "," + s; } } if (strRegion.Length > 0 && strRegion.Substring(0, 1) == ",") { strRegion = strRegion.Remove(0, 1); } string strSelCountryID = ""; foreach (string s in lblCountrys.Text.Trim().Split(',')) { if (s != "") { strSelCountryID += "," + s; } } if (strSelCountryID.Length > 0 && strSelCountryID.Substring(0, 1) == ",") { strSelCountryID = strSelCountryID.Remove(0, 1); } //string strSelCountryID = lblCountrys.Text.Trim(); //if (strSelCountryID.Length > 0 && strSelCountryID.Substring(0, 1) == ",") //{ // strSelCountryID = strSelCountryID.Remove(0, 1); //} //判斷選擇的區域最後是否有逗號 if (strRegion.Length > 0 && strRegion.Substring(strRegion.Length - 1, 1) == ",") { strRegion = strRegion.Remove(strRegion.Length - 1, 1); } //判斷選擇的國家最後是否有逗號 if (strSelCountryID.Length > 0 && strSelCountryID.Substring(strSelCountryID.Length - 1, 1) == ",") { strSelCountryID = strSelCountryID.Remove(strSelCountryID.Length - 1, 1); } SqlCommand cmd = new SqlCommand("STP_GetExportData"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@RID", strRegion); cmd.Parameters.AddWithValue("@CID", strSelCountryID); cmd.Parameters.AddWithValue("@PTID", ddlProductType.SelectedValue); cmd.Parameters.AddWithValue("@TecID", lblTechID.Text.Trim()); DataSet dsTech = SQLUtil.QueryDS(cmd); MemoryStream ms = new MemoryStream(); string strTitle = "WoWi_Technology_" + DateTime.Now.ToString("yyyyMMdd_HHmmss_") + User.Identity.Name; //Mark By Adams================================= //base.Response.Clear(); //base.Response.Buffer = true; //base.Response.Charset = "utf-8"; string strFile = System.Web.HttpUtility.UrlEncode(strTitle + ".xls", System.Text.Encoding.UTF8); //base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFile); //base.Response.ContentEncoding = Encoding.GetEncoding("utf-8"); //base.Response.ContentType = "application/ms-excel"; //Mark By Adams================================= //建立試算表 HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet; IRow row; //建立欄位名稱儲存格樣式 ICellStyle icsHeader = workbook.CreateCellStyle(); icsHeader.Alignment = HorizontalAlignment.CENTER; icsHeader.VerticalAlignment = VerticalAlignment.CENTER; icsHeader.WrapText = true; icsHeader.FillForegroundColor = HSSFColor.LIGHT_TURQUOISE.index; icsHeader.FillPattern = FillPatternType.SOLID_FOREGROUND; icsHeader.BorderBottom = CellBorderType.THIN; icsHeader.BorderLeft = CellBorderType.THIN; icsHeader.BorderRight = CellBorderType.THIN; icsHeader.BorderTop = CellBorderType.THIN; icsHeader.BottomBorderColor = HSSFColor.BLACK.index; icsHeader.LeftBorderColor = HSSFColor.BLACK.index; icsHeader.RightBorderColor = HSSFColor.BLACK.index; icsHeader.TopBorderColor = HSSFColor.BLACK.index; IFont iFont = workbook.CreateFont(); iFont.Color = HSSFColor.BLACK.index; iFont.FontHeightInPoints = 9; iFont.FontName = "Arial"; iFont.Boldweight = (short)FontBoldWeight.BOLD; icsHeader.SetFont(iFont); //建立文字儲存格樣式 ICellStyle icsTxt = workbook.CreateCellStyle(); icsTxt.Alignment = HorizontalAlignment.CENTER; icsTxt.VerticalAlignment = VerticalAlignment.CENTER; icsTxt.WrapText = true; icsTxt.BorderBottom = CellBorderType.THIN; icsTxt.BorderLeft = CellBorderType.THIN; icsTxt.BorderRight = CellBorderType.THIN; icsTxt.BorderTop = CellBorderType.THIN; icsTxt.BottomBorderColor = HSSFColor.BLACK.index; icsTxt.LeftBorderColor = HSSFColor.BLACK.index; icsTxt.RightBorderColor = HSSFColor.BLACK.index; icsTxt.TopBorderColor = HSSFColor.BLACK.index; IFont iFont1 = workbook.CreateFont(); iFont1.FontHeightInPoints = 9; iFont1.FontName = "Arial"; icsTxt.SetFont(iFont1); //判斷第16個模組是否有勾選匯出 //bool blnFrequency = chExportData.Items[chExportData.Items.Count - 1].Selected; bool blnFrequency; //選擇匯出的Technology foreach (ListItem liTechnology in cbTechnology.Items) { if (liTechnology.Selected) { blnFrequency = ((CheckBoxList)gvExportData.Rows[gvExportData.Rows.Count - 1].FindControl("chExportData")).Items[0].Selected; sheet = workbook.CreateSheet(liTechnology.Text.Replace("/", " ")); row = sheet.CreateRow(0); //建立表頭 int j = 0; string strExportData = ""; string strValue = ""; //foreach (ListItem liExportData in chExportData.Items) //{ // if (liExportData.Selected && liExportData.Value != "0") // { // //記錄勾選要匯出的欄位 // strExportData += ";" + liExportData.Value; // ICell cell; // if (j == 0) // { // cell = row.CreateCell(j); // strValue = "No."; // cell.SetCellValue(strValue); // cell.CellStyle = icsHeader; // //自動設定欄位寬度 // sheet.AutoSizeColumn(j); // j++; // } // cell = row.CreateCell(j); // strValue = liExportData.Value.Split('/')[2].ToString().Replace("_", " ").Replace("$", "/").Replace("@", ".").Replace("#td#", "-").Replace("#thl#", "(").Replace("#thr#", ")").Replace("#a#", "&"); // row.HeightInPoints = 24; // if (strValue.Contains("#br#")) // { // icsHeader.WrapText = true; // strValue = strValue.Replace("#br#", "\n"); // //因為換行所以將Row的高度變成4倍 // //row.HeightInPoints = 2 * sheet.DefaultRowHeight / 20; // //row.HeightInPoints = 24; // } // else { icsHeader.WrapText = true; } // cell.SetCellValue(strValue); // cell.CellStyle = icsHeader; // //自動設定欄位寬度 // sheet.AutoSizeColumn(j); // j++; // } //} foreach (GridViewRow gvr in gvExportData.Rows) { CheckBoxList cblExportData = (CheckBoxList)gvr.FindControl("chExportData"); int intMID = Convert.ToInt32(gvExportData.DataKeys[gvr.RowIndex].Value); if (cblExportData != null) { if (intMID != 16)//建立非第16個模組的Excel表頭 { foreach (ListItem liExportData in cblExportData.Items) { if (liExportData.Selected && liExportData.Value != "0") { //記錄勾選要匯出的欄位 strExportData += ";" + liExportData.Value; ICell cell; if (j == 0) { cell = row.CreateCell(j); strValue = "No."; cell.SetCellValue(strValue); cell.CellStyle = icsHeader; //自動設定欄位寬度 sheet.AutoSizeColumn(j); j++; } cell = row.CreateCell(j); strValue = liExportData.Value.Split('/')[2].ToString().Replace("_", " ").Replace("$", "/").Replace("@", ".").Replace("#td#", "-").Replace("#thl#", "(").Replace("#thr#", ")").Replace("#a#", "&"); row.HeightInPoints = 24; if (strValue.Contains("#br#")) { icsHeader.WrapText = true; strValue = strValue.Replace("#br#", "\n"); //因為換行所以將Row的高度變成4倍 //row.HeightInPoints = 2 * sheet.DefaultRowHeight / 20; //row.HeightInPoints = 24; } else { icsHeader.WrapText = true; } cell.SetCellValue(strValue); cell.CellStyle = icsHeader; //自動設定欄位寬度 sheet.AutoSizeColumn(j); j++; } } } } } //建立第16個模組表頭 DataTable dtFreqTitle; DataTable dtFreq; int intTitleIndex = j; string[] strTitle3 = { }; if (blnFrequency) { cmd = new SqlCommand("STP_ImaFrequencyTitle"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@wowi_tech_id", liTechnology.Value); dtFreqTitle = SQLUtil.QueryDS(cmd).Tables[0]; if (dtFreqTitle.Rows.Count > 0) { //int intTitleIndex = j; int intTitle2Index = 0; IRow row1 = sheet.CreateRow(1); IRow row2 = sheet.CreateRow(2); ICell cell; foreach (DataRow dr in dtFreqTitle.Rows) { strTitle3 = dr["Title3"].ToString().Split(';'); for (int i = 0; i <= strTitle3.Length - 1; i++) { //第0列 cell = row.CreateCell(intTitleIndex + i); cell.SetCellValue(dr["TechnologyCategoryName"].ToString()); cell.CellStyle = icsHeader; cell = row1.CreateCell(intTitleIndex + i); cell.SetCellValue(dr["Frequency"].ToString().Replace("#br#", "\n")); cell.CellStyle = icsHeader; if (dr["Frequency"].ToString().Contains("#br#")) { //因為換行所以將Row的高度變成3倍 row1.HeightInPoints = 3 * sheet.DefaultRowHeight / 20; } else { row1.HeightInPoints = 24; } cell = row2.CreateCell(intTitleIndex + i); cell.SetCellValue(strTitle3.GetValue(i).ToString()); cell.CellStyle = icsHeader; row2.HeightInPoints = 24; //自動設定欄位寬度 sheet.AutoSizeColumn(intTitleIndex + i); } intTitleIndex += strTitle3.Length; //合併第二列的欄位 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, j + intTitle2Index * strTitle3.Length, intTitleIndex - 1)); intTitle2Index++; //建立備註欄位 if (intTitle2Index == dtFreqTitle.Rows.Count && Convert.ToBoolean(dr["IsRemark"])) { cell = row.CreateCell(intTitleIndex); cell.CellStyle = icsHeader; cell = row1.CreateCell(intTitleIndex); cell.SetCellValue("Remark"); cell.CellStyle = icsHeader; cell = row2.CreateCell(intTitleIndex); cell.CellStyle = icsHeader; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, intTitleIndex, intTitleIndex)); //自動設定欄位寬度 sheet.AutoSizeColumn(intTitleIndex); //再加1是因為合併第一列欄位時減1的原因 intTitleIndex += 1; } } //合併第一列欄位 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, j, intTitleIndex - 1)); //建立第2及第3列前n欄表頭,並且上下欄位合併 for (int i = 1; i <= 2; i++) { row = sheet.GetRow(i); for (int k = 0; k < j; k++) { cell = row.CreateCell(k); cell.CellStyle = icsHeader; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, k, k)); } } } else//Technology沒有對應的Frequency { blnFrequency = false; } } //若沒有勾選模組16則資料從第1列開始寫入,若有則從第3列開始寫入 int intRowIndex = 1; if (blnFrequency) { intRowIndex = 3; } //將要匯出的欄位寫入一維陣列 if (strExportData.Length > 0) { strExportData = strExportData.Remove(0, 1); } string[] strColumn = strExportData.Split(';'); DataView dv; string strCountryID; string strRegionID; //建立資料intTitleIndex for (int i = 0; i <= dsTech.Tables[0].Rows.Count - 1; i++) { strCountryID = dsTech.Tables[0].Rows[i]["country_id"].ToString(); strRegionID = dsTech.Tables[0].Rows[i]["world_region_id"].ToString(); row = sheet.CreateRow(intRowIndex); for (int k = 0; k <= strColumn.Length; k++) { ICell cell; if (k == 0) { cell = row.CreateCell(k); cell.SetCellValue(i + 1); } else { cell = row.CreateCell(k); strValue = strColumn[k - 1].ToString(); DataTable dt; string str = ""; switch (strValue.Split('/')[1].ToString()) { case "0": strValue = dsTech.Tables[Convert.ToInt32(strValue.Split('/')[1])].Rows[i][strValue.Split('/')[2].ToString()].ToString(); break; //case "1":2012/10/05取消與Technology的關連 //case "2":2012/10/05取消與Technology的關連 //case "3":2012/10/05取消與Technology的關連 case "8": //CountryID及TechnologyID篩選 dv = dsTech.Tables[Convert.ToInt32(strValue.Split('/')[1])].DefaultView; //dv.RowFilter = "country_id=" + strCountryID + " and wowi_tech_id=" + liTechnology.Value; dv.RowFilter = "country_id=" + strCountryID; //判斷是否為Fee schedule 加入 wowi_tech_id is null if (strValue.Split('/')[1].ToString().Trim() == "8") { dv.RowFilter += " and (wowi_tech_id is null or wowi_tech_id=" + liTechnology.Value + ")"; } else { dv.RowFilter += " and wowi_tech_id=" + liTechnology.Value; } dt = dv.ToTable(); foreach (DataRow dr in dt.Rows) { str += "\n" + dr[strValue.Split('/')[2].ToString()].ToString(); } if (str.Length > 0) { strValue = str.Remove(0, 1); } else { strValue = ""; } break; case "1"://2012/10/05取消與Technology的關連 case "2"://2012/10/05取消與Technology的關連 case "3"://2012/10/05取消與Technology的關連 case "4": case "5": case "6": case "7": case "9": case "10": case "11": //CountryID篩選 dv = dsTech.Tables[Convert.ToInt32(strValue.Split('/')[1])].DefaultView; dv.RowFilter = "country_id=" + strCountryID; dt = dv.ToTable(); foreach (DataRow dr in dt.Rows) { str += "\n" + dr[strValue.Split('/')[2].ToString()].ToString(); } if (str.Length > 0) { strValue = str.Remove(0, 1); } else { strValue = ""; } break; } //判斷是否有換行符號 if (strValue.Contains("#br#")) { if (strValue.Substring(0, 4) == "#br#") { strValue = strValue.Remove(0, 4); } } cell.SetCellValue(strValue.Replace("#br#", "\n")); } cell.CellStyle = icsTxt; } //第16個模組資料 if (blnFrequency) { cmd = new SqlCommand("STP_ImaFrequencyGet"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@world_region_id", strRegionID); cmd.Parameters.AddWithValue("@country_id", strCountryID); cmd.Parameters.AddWithValue("@wowi_tech_id", liTechnology.Value); dtFreq = SQLUtil.QueryDS(cmd).Tables[0]; ICell cell; if (dtFreq.Rows.Count > 0) { int intTechnologyCategoryID = Convert.ToInt32(dtFreq.Rows[0]["TechnologyCategoryID"]); int intShowColumn = 0; int intCalcRow = 0;//記錄dtFreq跑迴圈到第幾筆資料 string[] strFreColumn = { "IsAllowedN", "PowerLimit", "DoorAllowed", "HT", "TPCDFS" }; //strTitle3 = dtFreq.Rows[0]["Title3"].ToString().Split(';'); intShowColumn = strTitle3.Length; foreach (DataRow dr in dtFreq.Rows) { for (int l = strColumn.Length + 1 + intShowColumn * intCalcRow; l <= strColumn.Length + intShowColumn + intShowColumn * intCalcRow; l++) { string strFreqValue = ""; for (int intK = 0; intK <= intShowColumn - 1; intK++) { cell = row.CreateCell(l); strFreqValue = dr[strFreColumn.GetValue(intK).ToString()].ToString().Trim(); if (strFreqValue.Contains("#br#")) { if (strFreqValue.Substring(0, 4) == "#br#") { strFreqValue = strFreqValue.Remove(0, 4); strFreqValue = strFreqValue.Replace("#br#", "\n"); } else { strFreqValue = strFreqValue.Replace("#br#", "\n"); } } cell.SetCellValue(strFreqValue); cell.CellStyle = icsTxt; l++; } } intCalcRow++; } //判斷是否有Remark備註資料 if (Convert.ToBoolean(dtFreq.Rows[0]["IsRemark"])) { cell = row.CreateCell(strColumn.Length + intShowColumn * intCalcRow + 1); cell.SetCellValue(dtFreq.Rows[0]["Remark"].ToString()); cell.CellStyle = icsTxt; } } else { for (int l = strColumn.Length + 1; l <= intTitleIndex - 1; l++) { cell = row.CreateCell(l); cell.CellStyle = icsTxt; } } } intRowIndex++; } } } //Add Export Flow by Adams 2012/11/12====================================================== string ExportPathNoEncrypt = System.Configuration.ConfigurationManager.AppSettings["IMAExportPathNoEncrypt"].ToString(); string ExportPathWithEncrypt = System.Configuration.ConfigurationManager.AppSettings["IMAExportPathWithEncrypt"].ToString(); FileStream fs = new FileStream(ExportPathNoEncrypt + strFile, FileMode.Create); workbook.Write(fs); //workbook.Write(ms); //Response.BinaryWrite(ms.ToArray()); sheet = null; workbook.Dispose(); workbook = null; ms.Flush(); ms.Position = 0; ms.Close(); ms.Dispose(); //base.Response.End(); fs.Close(); //產生加密的Excel檔 string openPassword = ExcelEncrypt.GenerateRandomCode(); string writePassword = ExcelEncrypt.GenerateRandomCode(); ExcelEncrypt.EncryptExcelByPassword(ExportPathNoEncrypt + strFile, ExportPathWithEncrypt + strFile, openPassword, openPassword); //傳送MAIL string mailfrom = "*****@*****.**"; string mailTo = System.Configuration.ConfigurationManager.AppSettings["IMAExportApprovor"].ToString(); string mailSubject = "[IMA Notice]" + User.Identity.Name + " had Export IMA Document at " + DateTime.Now.ToString(); string mailBody = "<br/> Dear Approver:" + "<br/> The password of IMA Export Excel File:" + openPassword + "<br/> Please refer to the attachment file"; MailUtil.SendMailWithAttachment(mailfrom, mailTo, mailSubject, mailBody, ExportPathWithEncrypt + strFile); //Add Export Flow by Adams 2012/11/12====================================================== Message.Text = "檔案 : " + strFile + " 匯出完成,請待主管審核確認!"; }
/// <summary> /// 将DataSet数据集转换HSSFworkbook对象,并保存为Stream流 /// </summary> /// <param name="ds"></param> /// <returns>返回数据流Stream对象</returns> public static MemoryStream ExportDatasetToExcel(DataSet ds, string fileName, string months) { #region //Hashtable ht = new Hashtable(); //ht.Add("orderNum", "订单号码"); //ht.Add("createTime", "创建日期"); //ht.Add("cityId", "订单城市"); //ht.Add("hotelName", "酒店名称"); //ht.Add("guestName", "入住人名称"); //ht.Add("inDate", "入住日期"); //ht.Add("outDate", "离店日期"); //ht.Add("bookTotalPrice", "房间单价"); //ht.Add("rooms", "间数"); //ht.Add("bookPrice", "总价"); //ht.Add("bookStatusOther", "订单状态"); //ht.Add("commission", "佣金"); //ht.Add("orderChannel", "渠道"); #endregion try { //文件流对象 MemoryStream stream = new MemoryStream(); //打开Excel对象 HSSFWorkbook workbook = new HSSFWorkbook(); //Excel的Sheet对象 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); sheet.SetColumnWidth(0, 12 * 256); sheet.SetColumnWidth(1, 10 * 256); sheet.SetColumnWidth(2, 12 * 256); sheet.SetColumnWidth(3, 12 * 256); sheet.SetColumnWidth(4, 12 * 256); sheet.SetColumnWidth(5, 5 * 256); sheet.SetColumnWidth(6, 9 * 256); sheet.SetColumnWidth(7, 9 * 256); sheet.SetColumnWidth(8, 9 * 256); sheet.SetColumnWidth(9, 8 * 256); sheet.SetMargin(MarginType.RightMargin, (double)0.1); sheet.SetMargin(MarginType.TopMargin, (double)0.1); sheet.SetMargin(MarginType.LeftMargin, (double)0.1); sheet.SetMargin(MarginType.BottomMargin, (double)0.1); sheet.PrintSetup.PaperSize = 9; sheet.PrintSetup.FitWidth = 800; sheet.PrintSetup.FitHeight = 1000; //set date format //CellStyle cellStyleDate = workbook.CreateCellStyle(); ICellStyle cellStyleDate = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); //使用NPOI操作Excel表 //合并消息头 NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cellHeader = rowHeader.CreateCell(0); cellHeader.SetCellValue("今夜酒店特价 HotelVP.com 佣金结算对账单"); cellStyleDate.Alignment = HorizontalAlignment.CENTER; cellStyleDate.VerticalAlignment = VerticalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeight = 20 * 20; cellStyleDate.SetFont(font); cellHeader.CellStyle = cellStyleDate; sheet.AddMergedRegion(new CellRangeAddress(0, 3, 0, 9)); //添加Excel头信息内容 //分销渠道:去哪儿 NPOI.SS.UserModel.ICell cellKey; NPOI.SS.UserModel.ICell cellValue; IFont font12 = workbook.CreateFont(); font12.FontHeightInPoints = 10; font12.FontName = "微软雅黑"; //font12.FontHeight = 20 * 20; NPOI.SS.UserModel.IRow channelRow = sheet.CreateRow(7); channelRow.Height = 20 * 20; cellKey = channelRow.CreateCell(0); cellKey.CellStyle.SetFont(font12); //cellKey.CellStyle.FillPattern = FillPatternType. cellKey.SetCellValue("To:"); cellValue = channelRow.CreateCell(1); cellValue.CellStyle.SetFont(font12); //cellValue.SetCellValue("去哪儿"); cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店名称"].ToString()); cellKey = channelRow.CreateCell(4); cellKey.SetCellValue("From:"); cellValue = channelRow.CreateCell(5); //cellValue.SetCellValue("去哪儿"); cellValue.SetCellValue("结算部"); //报表日期段:xxxx NPOI.SS.UserModel.IRow reportDateRow = sheet.CreateRow(8); cellKey = reportDateRow.CreateCell(0); cellKey.SetCellValue("Tel:"); cellValue = reportDateRow.CreateCell(1); //cellValue.SetCellValue("2012/12/01--2012/12/13"); cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店电话"].ToString()); cellKey = reportDateRow.CreateCell(4); cellKey.SetCellValue("Tel:"); cellValue = reportDateRow.CreateCell(5); //cellValue.SetCellValue("2012/12/01--2012/12/13"); cellValue.SetCellValue("021-52186026、52186036"); //报表生成日期:yyyy-mm-dd hh-mm-ss NPOI.SS.UserModel.IRow reportCreateDateRow = sheet.CreateRow(9); cellKey = reportCreateDateRow.CreateCell(0); cellKey.SetCellValue("Fax:"); cellValue = reportCreateDateRow.CreateCell(1); cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店传真"].ToString()); cellKey = reportCreateDateRow.CreateCell(4); cellKey.SetCellValue("Fax:"); cellValue = reportCreateDateRow.CreateCell(5); cellValue.SetCellValue("021-52186011"); //结算总价 NPOI.SS.UserModel.IRow SettlementPriceRow = sheet.CreateRow(10); cellKey = SettlementPriceRow.CreateCell(0); cellKey.SetCellValue("Id:"); cellValue = SettlementPriceRow.CreateCell(1); //cellValue.SetCellValue("111122233"); cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店ID"].ToString()); cellKey = SettlementPriceRow.CreateCell(4); cellKey.SetCellValue("Email:"); cellValue = SettlementPriceRow.CreateCell(5); //cellValue.SetCellValue("111122233"); cellValue.SetCellValue("*****@*****.**"); //结算总价 NPOI.SS.UserModel.IRow remarkRow = sheet.CreateRow(12); cellKey = remarkRow.CreateCell(0); cellKey.SetCellValue("您好!以下为2013年" + months + "月佣金结算对账单,烦请贵酒店相关负责人员核对以下数据"); NPOI.SS.UserModel.IRow remark1Row = sheet.CreateRow(13); cellKey = remark1Row.CreateCell(0); cellKey.SetCellValue("若无误请于收到对账单二日内签字盖章确认回传,若有疑问,请及时告知,非常感谢!"); //结算总价 NPOI.SS.UserModel.IRow totalPrice1Row = sheet.CreateRow(16); cellKey = totalPrice1Row.CreateCell(0); cellKey.SetCellValue("总间夜数:"); NPOI.SS.UserModel.ICell cellValue1; cellValue1 = totalPrice1Row.CreateCell(1); //cellValue1.CellStyle.Alignment = HorizontalAlignment.CENTER; cellValue1.SetCellValue(ds.Tables[0].Compute("SUM(间夜)", "").ToString()); cellValue = totalPrice1Row.CreateCell(2); cellValue.SetCellValue("间夜"); cellValue = totalPrice1Row.CreateCell(4); cellValue.SetCellValue("签字确认:"); cellValue = totalPrice1Row.CreateCell(5); cellValue.SetCellValue(""); NPOI.SS.UserModel.IRow totalPrice2Row = sheet.CreateRow(17); cellKey = totalPrice2Row.CreateCell(0); cellKey.SetCellValue("佣金合计:"); NPOI.SS.UserModel.ICell cellValue2; cellValue2 = totalPrice2Row.CreateCell(1); //cellValue2.CellStyle.Alignment = HorizontalAlignment.CENTER; cellValue2.SetCellValue(ds.Tables[0].Compute("SUM(佣金)", "").ToString()); cellValue = totalPrice2Row.CreateCell(2); cellValue.SetCellValue("人民币(元)"); cellValue = totalPrice2Row.CreateCell(4); cellValue.SetCellValue("发票抬头:"); cellValue = totalPrice2Row.CreateCell(5); cellValue.SetCellValue(""); NPOI.SS.UserModel.IRow totalPrice3Row = sheet.CreateRow(18); cellKey = totalPrice3Row.CreateCell(0); cellKey.SetCellValue("前期未付佣金:"); cellValue = totalPrice3Row.CreateCell(1); cellValue.SetCellValue(""); cellValue = totalPrice3Row.CreateCell(2); cellValue.SetCellValue("人民币(元)"); NPOI.SS.UserModel.IRow row = sheet.CreateRow(21); int count = 0; for (int i = 4; i < ds.Tables[0].Columns.Count; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.ICell cell = row.CreateCell(count++); cell.SetCellValue(ds.Tables[0].Columns[i].ColumnName.ToString()); } //将数据导入到excel表中 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { NPOI.SS.UserModel.IRow rows = sheet.CreateRow(i + 22); count = 0; for (int j = 4; j < ds.Tables[0].Columns.Count; j++) { if (ds.Tables[0].Columns[j].ColumnName != null) { NPOI.SS.UserModel.ICell cell = rows.CreateCell(count++); Type type = ds.Tables[0].Rows[i][j].GetType(); if (type == typeof(int) || type == typeof(Int16) || type == typeof(Int32) || type == typeof(Int64)) { cell.SetCellValue((int)ds.Tables[0].Rows[i][j]); } else { if (type == typeof(float) || type == typeof(double) || type == typeof(Double)) { cell.SetCellValue((Double)ds.Tables[0].Rows[i][j]); } else { if (type == typeof(DateTime)) { cell.SetCellValue(((DateTime)ds.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd HH:mm")); } else { if (type == typeof(bool) || type == typeof(Boolean)) { cell.SetCellValue((bool)ds.Tables[0].Rows[i][j]); } else { cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString()); } } } } } } } NPOI.SS.UserModel.IRow totalInfo1Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 1); cellValue = totalInfo1Row.CreateCell(0); cellValue.SetCellValue("Total:"); cellValue = totalInfo1Row.CreateCell(5); cellValue.SetCellValue(ds.Tables[0].Compute("SUM(间夜)", "").ToString()); cellValue = totalInfo1Row.CreateCell(6); cellValue.SetCellValue(ds.Tables[0].Compute("SUM(卖价)", "").ToString()); cellValue = totalInfo1Row.CreateCell(7); cellValue.SetCellValue(ds.Tables[0].Compute("SUM(底价)", "").ToString()); cellValue = totalInfo1Row.CreateCell(8); cellValue.SetCellValue(ds.Tables[0].Compute("SUM(佣金)", "").ToString()); //结算总价 NPOI.SS.UserModel.IRow companyinfoRow = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 5); cellKey = companyinfoRow.CreateCell(0); cellKey.SetCellValue("我司汇款资料"); NPOI.SS.UserModel.IRow companyinfo1Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 6); cellValue = companyinfo1Row.CreateCell(0); cellValue.SetCellValue("银行账号:"); cellValue = companyinfo1Row.CreateCell(1); cellValue.SetCellValue("31001515100050024076"); NPOI.SS.UserModel.IRow companyinfo2Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 7); cellValue = companyinfo2Row.CreateCell(0); cellValue.SetCellValue("开户银行:"); cellValue = companyinfo2Row.CreateCell(1); cellValue.SetCellValue("中国建设银行股份有限公司上海长宁支行"); NPOI.SS.UserModel.IRow companyinfo3Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 8); cellValue = companyinfo3Row.CreateCell(0); cellValue.SetCellValue("公司全称:"); cellValue = companyinfo3Row.CreateCell(1); cellValue.SetCellValue("上海千栈网络信息科技有限公司"); //保存excel文档 sheet.ForceFormulaRecalculation = true; using (Stream filstream = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { workbook.Write(filstream); } workbook.Dispose(); return stream; } catch (Exception ex) { return new MemoryStream(); } }
public static MemoryStream ExportToExcel(List<Group> list, List<string> date) { try { //文件流对像 MemoryStream stream = new MemoryStream(); //打开Excel对象 HSSFWorkbook workbook = new HSSFWorkbook(); Font aFont = workbook.CreateFont(); aFont.FontName = "Calibri"; aFont.FontHeightInPoints = 14; //aFont.Boldweight = short.MaxValue; CellStyle projectStyle = workbook.CreateCellStyle(); //对齐方式 projectStyle.Alignment = HorizontalAlignment.CENTER; projectStyle.VerticalAlignment = VerticalAlignment.BOTTOM; ////边框 projectStyle.BorderTop = CellBorderType.MEDIUM; projectStyle.BorderRight = CellBorderType.MEDIUM; projectStyle.BorderBottom = CellBorderType.THIN; projectStyle.BorderLeft = CellBorderType.MEDIUM; //字体 projectStyle.SetFont(aFont); //背景颜色 HSSFPalette palette = workbook.GetCustomPalette(); palette.SetColorAtIndex((short)9, (byte)(240), (byte)(240), (byte)(240)); palette.SetColorAtIndex((short)10, (byte)(220), (byte)(220), (byte)(220)); projectStyle.FillForegroundColor=(short)9; projectStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; //projectStyle.FillBackgroundColor = (short)9; CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; headStyle.VerticalAlignment = VerticalAlignment.BOTTOM; headStyle.BorderTop = CellBorderType.MEDIUM; headStyle.BorderRight = CellBorderType.THIN; headStyle.SetFont(aFont); //headStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; //headStyle.FillPattern = FillPatternType.SQUARES; //headStyle.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index; headStyle.FillForegroundColor = (short)9; headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; CellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.Alignment = HorizontalAlignment.CENTER; dateStyle.VerticalAlignment = VerticalAlignment.BOTTOM; dateStyle.BorderTop = CellBorderType.MEDIUM; dateStyle.BorderRight = CellBorderType.MEDIUM; dateStyle.BorderLeft = CellBorderType.MEDIUM; Font bFont = workbook.CreateFont(); bFont.FontName = "Calibri"; bFont.FontHeightInPoints = 12; dateStyle.SetFont(bFont); CellStyle projectstyle = workbook.CreateCellStyle(); //对齐方式 projectstyle.Alignment = HorizontalAlignment.CENTER; projectstyle.VerticalAlignment = VerticalAlignment.CENTER; ////边框 projectstyle.BorderRight = CellBorderType.MEDIUM; projectstyle.BorderBottom = CellBorderType.MEDIUM; //projectstyle.BorderLeft = CellBorderType.MEDIUM; projectstyle.SetFont(bFont); CellStyle nameStyle = workbook.CreateCellStyle(); nameStyle.SetFont(bFont); //对齐方式 nameStyle.Alignment = HorizontalAlignment.CENTER; nameStyle.VerticalAlignment = VerticalAlignment.CENTER; ////边框 nameStyle.BorderTop = CellBorderType.THIN; nameStyle.BorderRight = CellBorderType.THIN; nameStyle.BorderBottom = CellBorderType.THIN; nameStyle.BorderLeft = CellBorderType.MEDIUM; CellStyle itemStyle = workbook.CreateCellStyle(); nameStyle.SetFont(bFont); //对齐方式 //itemStyle.Alignment = HorizontalAlignment.CENTER; itemStyle.VerticalAlignment = VerticalAlignment.BOTTOM; ////边框 itemStyle.BorderTop = CellBorderType.THIN; itemStyle.BorderRight = CellBorderType.THIN; itemStyle.BorderBottom = CellBorderType.THIN; itemStyle.BorderLeft = CellBorderType.THIN; CellStyle datestyle = workbook.CreateCellStyle(); datestyle.SetFont(bFont); //对齐方式 datestyle.Alignment = HorizontalAlignment.RIGHT; datestyle.VerticalAlignment = VerticalAlignment.BOTTOM; ////边框 datestyle.BorderTop = CellBorderType.THIN; datestyle.BorderRight = CellBorderType.MEDIUM; datestyle.BorderBottom = CellBorderType.THIN; datestyle.BorderLeft = CellBorderType.MEDIUM; CellStyle emptyStyle = workbook.CreateCellStyle(); //对齐方式 emptyStyle.Alignment = HorizontalAlignment.CENTER; emptyStyle.VerticalAlignment = VerticalAlignment.BOTTOM; //背景颜色 emptyStyle.FillForegroundColor = (short)10; emptyStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; ////边框 emptyStyle.BorderTop = CellBorderType.THIN; emptyStyle.BorderRight = CellBorderType.MEDIUM; emptyStyle.BorderBottom = CellBorderType.THIN; emptyStyle.BorderLeft = CellBorderType.MEDIUM; //emptyStyle.FillBackgroundColor = (short)8; CellStyle lastLineStyle = workbook.CreateCellStyle(); lastLineStyle.BorderTop = CellBorderType.MEDIUM; //Excel的Sheet对象 Sheet sheet = workbook.CreateSheet("sheet1"); sheet.SetColumnWidth(0, 22 * 256); sheet.SetColumnWidth(1, 20 * 256); sheet.SetColumnWidth(2, 24 * 256); sheet.DefaultRowHeightInPoints = 20; sheet.CreateFreezePane(3, 0);//Column Locking //sheet.CreateFreezePane(3, 0, 0, 0); ////set date format //CellStyle cellStyleDate = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); //将数据导入到excel表中 Row row; Cell cell; CellRangeAddress range; int count = 1; foreach (var project in list) { int column = 0; row = sheet.CreateRow(count); cell = row.CreateCell(0); cell.SetCellValue("Project"); cell.CellStyle = projectStyle; cell = row.CreateCell(1); cell.SetCellValue("Name"); cell.CellStyle = headStyle; cell = row.CreateCell(2); cell.SetCellValue("Check Item"); cell.CellStyle = headStyle; foreach (var dateitem in date) { sheet.SetColumnWidth(column + 3, 12 * 256); cell = row.CreateCell(column + 3); cell.SetCellValue(dateitem); cell.CellStyle = dateStyle; column++; } count++; int startCount = count; foreach (var user in project.Monthly) { Row row1 = sheet.CreateRow(count); Row row2 = sheet.CreateRow(count + 1); Row row3 = sheet.CreateRow(count + 2); Row row4 = sheet.CreateRow(count + 3); cell = row1.CreateCell(0); cell.SetCellValue(project.GroupName); cell.CellStyle = projectstyle; //cell = row2.CreateCell(0); //cell.CellStyle = projectstyle; //cell = row3.CreateCell(0); //cell.CellStyle = projectstyle; //cell = row4.CreateCell(0); //cell.CellStyle = projectstyle; cell = row1.CreateCell(1); cell.SetCellValue(user.UserName); cell.CellStyle = nameStyle; cell = row2.CreateCell(1); cell.CellStyle = nameStyle; cell = row3.CreateCell(1); cell.CellStyle = nameStyle; cell = row4.CreateCell(1); cell.CellStyle = nameStyle; cell = row1.CreateCell(2); cell.SetCellValue("Work Done"); cell.CellStyle = itemStyle; cell = row2.CreateCell(2); cell.SetCellValue("Bug Created"); cell.CellStyle = itemStyle; cell = row3.CreateCell(2); cell.SetCellValue("Work Quality"); cell.CellStyle = itemStyle; cell = row4.CreateCell(2); cell.SetCellValue("Daily Performance"); cell.CellStyle = itemStyle; range = new CellRangeAddress(count, count + 3, 1, 1); sheet.AddMergedRegion(range); int num = 0; foreach (var item in user.MonthlyAppraisal) { cell = row1.CreateCell(num + 3); if (item.WorkDone != 0) { cell.SetCellValue((Double)item.WorkDone); cell.CellStyle = datestyle; } else { cell.CellStyle = emptyStyle; } cell = row2.CreateCell(num + 3); if (item.BugCreated != 0) { cell.SetCellValue((Double)item.BugCreated); cell.CellStyle = datestyle; } else { cell.CellStyle = emptyStyle; } cell = row3.CreateCell(num + 3); if (item.WorkQuality != 0) { cell.SetCellValue((Double)item.WorkQuality); cell.CellStyle = datestyle; } else { cell.CellStyle = emptyStyle; } cell = row4.CreateCell(num + 3); if (item.DailyPerformance != 0) { cell.SetCellValue((Double)item.DailyPerformance); cell.CellStyle = datestyle; } else { cell.CellStyle = emptyStyle; } num++; } count += 4; } range = new CellRangeAddress(startCount, count - 1, 0, 0); sheet.AddMergedRegion(range); } row = sheet.CreateRow(count); int m = date.Count; for (int i = 0; i < m + 3; i++) { cell = row.CreateCell(i); cell.CellStyle = lastLineStyle; } //保存excel文档 sheet.ForceFormulaRecalculation = true; workbook.Write(stream); workbook.Dispose(); return stream; } catch { return new MemoryStream(); } }
public static MemoryStream ExportDatasetToExcel(List<Tuple<string, List<Tuple<string, string>>>> list) { try { //File stream object MemoryStream stream = new MemoryStream(); //Open Excel object HSSFWorkbook workbook = new HSSFWorkbook(); //Head Style CellStyle headstyle = workbook.CreateCellStyle(); //Alignment headstyle.Alignment = HorizontalAlignment.CENTER; headstyle.VerticalAlignment = VerticalAlignment.CENTER; //Font Font headfont = workbook.CreateFont(); headfont.FontHeightInPoints = 12; headfont.Boldweight = short.MaxValue; headstyle.SetFont(headfont); //Background color headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; headstyle.FillPattern = FillPatternType.SQUARES; headstyle.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index; //Border headstyle.BorderTop = CellBorderType.THIN; headstyle.BorderRight = CellBorderType.THIN; headstyle.BorderBottom = CellBorderType.THIN; headstyle.BorderLeft = CellBorderType.THIN; //Body Style CellStyle bodystyle = workbook.CreateCellStyle(); //Border bodystyle.BorderTop = CellBorderType.THIN; bodystyle.BorderRight = CellBorderType.THIN; bodystyle.BorderBottom = CellBorderType.THIN; bodystyle.BorderLeft = CellBorderType.THIN; //Line Feed bodystyle.WrapText = true; //Sheet's object of Excel Sheet sheet = workbook.CreateSheet("sheet1"); sheet.SetColumnWidth(0, 5000); sheet.SetColumnWidth(1, 100000); ////set date format //CellStyle cellStyleDate = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); //Export to Excel Row row; Cell cell; //cell.SetCellType(); int count = 0; foreach (var l in list) { if (l.Item2.Count != 0) { row = sheet.CreateRow(count); row.HeightInPoints = 20; cell = row.CreateCell(0); cell.CellStyle = headstyle; cell.SetCellValue(l.Item1); cell = row.CreateCell(1); cell.CellStyle = headstyle; CellRangeAddress range = new CellRangeAddress(count, count, 0, 1); sheet.AddMergedRegion(range); count++; foreach (var item in l.Item2) { row = sheet.CreateRow(count); cell = row.CreateCell(0); cell.SetCellValue(item.Item1); cell.CellStyle = bodystyle; cell = row.CreateCell(1); cell.SetCellValue(item.Item2); cell.CellStyle = bodystyle; count++; } } } //Save excel sheet.ForceFormulaRecalculation = true; workbook.Write(stream); workbook.Dispose(); return stream; } catch { return new MemoryStream(); } }
public static MemoryStream ExportDataToExcel(List<Tuple<string, string, string>> list) { try { //File stream object MemoryStream stream = new MemoryStream(); //Open Excel object HSSFWorkbook workbook = new HSSFWorkbook(); //custom color HSSFPalette palette = workbook.GetCustomPalette(); palette.SetColorAtIndex((short)15, (byte)(184), (byte)(134), (byte)(11)); palette.SetColorAtIndex((short)16, (byte)(255), (byte)(240), (byte)(215)); //Title Style CellStyle titlestyle = workbook.CreateCellStyle(); //Alignment titlestyle.Alignment = HorizontalAlignment.CENTER; titlestyle.VerticalAlignment = VerticalAlignment.CENTER; //Font Font titlefont = workbook.CreateFont(); titlefont.FontHeightInPoints = 16; titlefont.FontName = "Calibri"; titlefont.Color = HSSFColor.WHITE.index; titlefont.Boldweight = (short)FontBoldWeight.BOLD; titlestyle.SetFont(titlefont); //Background color titlestyle.FillForegroundColor = (short)15; titlestyle.FillPattern = FillPatternType.SOLID_FOREGROUND; //Border titlestyle.BorderTop = CellBorderType.THIN; titlestyle.BorderRight = CellBorderType.THIN; titlestyle.BorderBottom = CellBorderType.THIN; titlestyle.BorderLeft = CellBorderType.THIN; //Head Style CellStyle headstyle = workbook.CreateCellStyle(); //Alignment headstyle.Alignment = HorizontalAlignment.CENTER; headstyle.VerticalAlignment = VerticalAlignment.CENTER; //Font Font headfont = workbook.CreateFont(); headfont.FontHeightInPoints = 14; headfont.FontName = "Calibri"; headfont.Boldweight = (short)FontBoldWeight.BOLD; headstyle.SetFont(headfont); //Background color headstyle.FillForegroundColor = (short)16; headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND; //Border headstyle.BorderTop = CellBorderType.THIN; headstyle.BorderRight = CellBorderType.THIN; headstyle.BorderBottom = CellBorderType.THIN; headstyle.BorderLeft = CellBorderType.THIN; //Name Style CellStyle namestyle = workbook.CreateCellStyle(); //Alignment namestyle.Alignment = HorizontalAlignment.CENTER; namestyle.VerticalAlignment = VerticalAlignment.CENTER; //Font Font namefont = workbook.CreateFont(); namefont.FontHeightInPoints = 11; namefont.FontName = "Calibri"; namefont.Boldweight = (short)FontBoldWeight.BOLD; namestyle.SetFont(namefont); //Border namestyle.BorderTop = CellBorderType.THIN; namestyle.BorderRight = CellBorderType.THIN; namestyle.BorderBottom = CellBorderType.THIN; namestyle.BorderLeft = CellBorderType.THIN; //Body Style CellStyle bodystyle = workbook.CreateCellStyle(); //Alignment bodystyle.VerticalAlignment = VerticalAlignment.CENTER; //Font Font bodyfont = workbook.CreateFont(); bodyfont.FontHeightInPoints = 12; bodyfont.FontName = "Times New Roman"; bodystyle.SetFont(bodyfont); //Border bodystyle.BorderTop = CellBorderType.THIN; bodystyle.BorderRight = CellBorderType.THIN; bodystyle.BorderBottom = CellBorderType.THIN; bodystyle.BorderLeft = CellBorderType.THIN; //Line Feed bodystyle.WrapText = true; //Sheet's object of Excel Sheet sheet = workbook.CreateSheet("sheet1"); sheet.SetColumnWidth(0, (short)(35.7 * 160)); sheet.SetColumnWidth(1, (short)(35.7 * 400)); sheet.SetColumnWidth(2, (short)(35.7 * 600)); //Export to Excel Row row; Cell cell; //cell.SetCellType(); int count = 2; row = sheet.CreateRow(0); row.HeightInPoints = 20; cell = row.CreateCell(0); cell.CellStyle = titlestyle; cell.SetCellValue("Staff Report"); cell = row.CreateCell(1); cell.CellStyle = titlestyle; cell = row.CreateCell(2); cell.CellStyle = titlestyle; CellRangeAddress range = new CellRangeAddress(0, 0, 0, 2); sheet.AddMergedRegion(range); row = sheet.CreateRow(1); row.HeightInPoints = 20; cell = row.CreateCell(0); cell.CellStyle = headstyle; cell = row.CreateCell(1); cell.CellStyle = headstyle; cell.SetCellValue(" What you worked on today?"); cell = row.CreateCell(2); cell.CellStyle = headstyle; cell.SetCellValue(" What you will be working on tomorrow?"); foreach (var item in list) { row = sheet.CreateRow(count); cell = row.CreateCell(0); cell.SetCellValue(item.Item1); cell.CellStyle = namestyle; cell = row.CreateCell(1); cell.SetCellValue(item.Item2); cell.CellStyle = bodystyle; cell = row.CreateCell(2); cell.SetCellValue(item.Item3); cell.CellStyle = bodystyle; count++; } //Save excel sheet.ForceFormulaRecalculation = true; workbook.Write(stream); workbook.Dispose(); return stream; } catch { return new MemoryStream(); } }