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);
}
