public bool SetPrintArea(string reference) { if (hssfworkbook == null) { return(false); } try { hssfworkbook.SetPrintArea(0, reference); return(true); } catch { throw; } }
static void Main(string[] args) { InitializeWorkbook(); ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); //fill some data sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample"); int x = 1; int i = 1; for (i = 1; i <= 15; i++) { IRow row = sheet1.CreateRow(i); for (int j = 0; j < 15; j++) { row.CreateCell(j).SetCellValue(x++); } } //set print area hssfworkbook.SetPrintArea(0, "A5:G20"); WriteToFile(); }
/// <summary> /// 设置打印区域 /// </summary> /// <param name="Sheetindex"></param> /// <param name="Area">"A5:G20"</param> public void SetPrintAreaInXls(int Sheetindex, string Area) { InitializeWorkbook(); hssfworkbook.SetPrintArea(Sheetindex, Area); WriteToFile(); }
public void TestMultiplePrintAreaFile() { HSSFWorkbook workbook = new HSSFWorkbook(); workbook.CreateSheet("Sheet1"); workbook.CreateSheet("Sheet2"); workbook.CreateSheet("Sheet3"); String reference1 = "$A$1:$B$1"; String reference2 = "$B$2:$D$5"; String reference3 = "$D$2:$F$5"; workbook.SetPrintArea(0, reference1); workbook.SetPrintArea(1, reference2); workbook.SetPrintArea(2, reference3); //Check Created print areas String retrievedPrintArea; retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 1)"); Assert.AreEqual("Sheet1!" + reference1, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(1); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 2)"); Assert.AreEqual("Sheet2!" + reference2, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(2); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 3)"); Assert.AreEqual("Sheet3!" + reference3, retrievedPrintArea); // Check print areas after re-reading workbook workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 1)"); Assert.AreEqual("Sheet1!" + reference1, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(1); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 2)"); Assert.AreEqual("Sheet2!" + reference2, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(2); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 3)"); Assert.AreEqual("Sheet3!" + reference3, retrievedPrintArea); }
public void TestPrintAreaCoords() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = sheetName + "!$A$1:$B$1"; workbook.SetPrintArea(0, 0, 1, 0, 0); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); Assert.AreEqual("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea); }
public void TestPrintAreaUnion() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = "$A$1:$B$1,$D$1:$F$2"; workbook.SetPrintArea(0, reference); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); Assert.AreEqual("'Test Print Area'!$A$1:$B$1,'Test Print Area'!$D$1:$F$2", retrievedPrintArea); }
public void TestPrintAreaRemove() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = sheetName + "!$A$1:$B$1"; workbook.SetPrintArea(0, 0, 1, 0, 0); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); workbook.RemovePrintArea(0); Assert.IsNull(workbook.GetPrintArea(0), "PrintArea was not Removed"); }
public void TestPrintAreaFile() { HSSFWorkbook workbook = new HSSFWorkbook(); workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = "$A$1:$B$1"; workbook.SetPrintArea(0, reference); workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); Assert.AreEqual("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea, "References Match"); }
public string GerarSenhaCoordenador(Int32 coordenador) { Int32 totalProjetos = 0; HSSFWorkbook wb; // = new HSSFWorkbook(); HSSFSheet sh; // = (HSSFSheet)wb.GetSheet("Extrato"); // sh.rep try { string x = HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings.Get("pathsenhacoordenador")); using (FileStream file = new FileStream(x, FileMode.Open, FileAccess.Read)) { wb = new HSSFWorkbook(file); file.Close(); sh = (HSSFSheet)wb.GetSheetAt(0); } } catch (Exception ex) { throw new Exception("Erro ao abrir o arquivo de modelo. Processo encerrado. Erro " + ex.Message); } Negocio.coordenadorNegocio objCoordenador = new coordenadorNegocio(); objCoordenador.GetCoordenadorById(coordenador); sh.GetRow(1).GetCell(0).SetCellValue(objCoordenador.banco.tabela.Rows[0]["nome"].ToString().Trim().ToUpper()); //sh.GetRow(2).GetCell(1).SetCellValue("Senha: " + objCoordenador.banco.tabela.Rows[0]["senha"].ToString().Trim()); Negocio.projetoNegocios objProjeto = new projetoNegocios(); objProjeto.GetProjetos(coordenador); Int32 numlinha = 4; foreach (DataRow r in objProjeto.banco.tabela.Rows) { totalProjetos++; //var linha = sh.CreateRow(numlinha); sh.GetRow(numlinha).GetCell(0).SetCellValue(r["projeto"].ToString().Trim()); // sh.GetRow(numlinha).GetCell(1).SetCellValue(r["conta_principal"].ToString().Trim()); numlinha++; } HSSFFont hFont = (HSSFFont)wb.CreateFont(); hFont.FontHeightInPoints = 14; hFont.FontName = "Calibri"; hFont.Underline = FontUnderlineType.Double; hFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; HSSFCellStyle hStyle = (HSSFCellStyle)wb.CreateCellStyle(); hStyle.SetFont(hFont); numlinha += 2; sh.GetRow(numlinha).GetCell(1).SetCellValue("Estes são os seus dados para realizar o acesso via internet"); sh.GetRow(numlinha).GetCell(1).CellStyle = hStyle; sh.GetRow(numlinha).RemoveCell(sh.GetRow(numlinha).GetCell(2)); sh.GetRow(numlinha).RemoveCell(sh.GetRow(numlinha).GetCell(3)); sh.GetRow(numlinha).RemoveCell(sh.GetRow(numlinha).GetCell(4)); numlinha++; sh.GetRow(numlinha).GetCell(2).SetCellValue("Código"); sh.GetRow(numlinha).GetCell(4).SetCellValue("Senha"); numlinha++; sh.GetRow(numlinha).GetCell(2).SetCellValue(objCoordenador.banco.tabela.Rows[0]["coordenador"].ToString().Trim().ToUpper()); sh.GetRow(numlinha).GetCell(4).SetCellValue(objCoordenador.banco.tabela.Rows[0]["senha"].ToString().Trim().ToUpper()); //for (int i = 0; i <= 7; i++) //{ // sh.GetRow(numlinha).GetCell(i).CellStyle.BorderTop = BorderStyle.Medium; // sh.GetRow(numlinha + 4).GetCell(i).CellStyle.BorderTop = BorderStyle.Medium; //} //for (int i = numlinha - 4; i <= numlinha; i++) //{ // sh.GetRow(i).GetCell(0).CellStyle.BorderLeft = BorderStyle.Medium; // sh.GetRow(i).GetCell(7).CellStyle.BorderRight = BorderStyle.Medium; //} wb.SetPrintArea(0, 0, 7, 0, numlinha); //sh.PrintSetup.PaperSize = (short)PaperSize.A4; sh.FitToPage = false;// RowBreak(8); Char delimiter = ' '; ExportarArquivo(wb, "SenhaCoordenador_" + objCoordenador.banco.tabela.Rows[0]["nome"].ToString().Split(delimiter)[0].Trim() + ".xls"); return(""); }
public void SetPrintArea(int k, string printsetup) { k = k - 1; string Result = ""; ISheet sheet = wb.GetSheetAt(k); PrintSet printset = JsonConvert.DeserializeObject <PrintSet>(printsetup); sheet.PrintSetup.Scale = printset.Scale; sheet.PrintSetup.PaperSize = printset.PaperSize; //Console.WriteLine("Printarea="+ printset.Printarea); //Console.WriteLine("GetPrintArea=" + wb.GetPrintArea(k)); string printarea = printset.Printarea; //设置页眉页脚 if (!string.IsNullOrEmpty(printset.HL)) { sheet.Header.Left = printset.HL; } if (!string.IsNullOrEmpty(printset.HR)) { sheet.Header.Right = printset.HR; } if (!string.IsNullOrEmpty(printset.HC)) { sheet.Header.Center = printset.HC; } if (!string.IsNullOrEmpty(printset.FL)) { sheet.Footer.Left = printset.FL; } if (!string.IsNullOrEmpty(printset.FR)) { sheet.Footer.Right = printset.FR; } if (!string.IsNullOrEmpty(printset.FC)) { sheet.Footer.Center = printset.FC; } //获取页边距 sheet.SetMargin(MarginType.TopMargin, printset.tmargin); sheet.SetMargin(MarginType.RightMargin, printset.rmargin); sheet.SetMargin(MarginType.BottomMargin, printset.bmargin); sheet.SetMargin(MarginType.LeftMargin, printset.lmargin); sheet.SetMargin(MarginType.HeaderMargin, printset.hmargin); sheet.SetMargin(MarginType.FooterMargin, printset.fmargin); //设置打印预览分页 //sheet.FitToPage = false; //sheet.SetRowBreak(10); //分行 //设置打印区域[只需要获取模板信息] //Console.WriteLine("printarea=" + printarea); if (string.IsNullOrEmpty(printarea)) { return; } int index = printarea.IndexOf("!"); if (index == -1) { return; } var vs = printarea.Split('$'); string parea = ""; switch (vs.Length) { case 5: vs[4] = sheet.LastRowNum.ToString(); parea = "$" + vs[1] + "$" + vs[2] + "$" + vs[3] + "$" + vs[4]; break; default: break; } //Console.WriteLine("LastRowNum=" + sheet.LastRowNum.ToString()); //Console.WriteLine("parea=" + parea); //int colindex = printarea.IndexOf(":"); //string colnum = printarea.Substring(colindex + 1, 1); //string num = printarea.Substring(colindex + 2, printarea.Length - colindex - 2); //string parea = printarea.Substring(index+1, printarea.Length-index-1); wb.SetPrintArea(k, parea); }