package art.servers.gost.access.reports;
|
|
import art.servers.ServerException;
|
import art.servers.Shared;
|
import art.servers.gost.access.configuration.Configuration;
|
import com.sun.star.beans.PropertyValue;
|
import com.sun.star.frame.XComponentLoader;
|
import com.sun.star.frame.XDesktop;
|
import com.sun.star.frame.XStorable;
|
import com.sun.star.lang.XComponent;
|
import com.sun.star.lang.XMultiComponentFactory;
|
import com.sun.star.lib.uno.adapter.ByteArrayToXInputStreamAdapter;
|
import com.sun.star.lib.uno.adapter.OutputStreamToXOutputStreamAdapter;
|
import com.sun.star.uno.UnoRuntime;
|
import com.sun.star.uno.XComponentContext;
|
import java.io.ByteArrayOutputStream;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.PrintStream;
|
import java.text.SimpleDateFormat;
|
import java.util.Calendar;
|
import java.util.TimeZone;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
import ooo.connector.BootstrapSocketConnector;
|
import org.apache.poi.ss.usermodel.BorderStyle;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.ss.util.CellReference;
|
import org.apache.poi.ss.util.RegionUtil;
|
import org.apache.poi.xssf.streaming.SXSSFCell;
|
import org.apache.poi.xssf.streaming.SXSSFRow;
|
import org.apache.poi.xssf.streaming.SXSSFSheet;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFCell;
|
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
|
import org.apache.poi.xssf.usermodel.XSSFRow;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
public class XLSX
|
{
|
protected XSSFWorkbook workbook;
|
protected String language = null;
|
|
|
public XLSX ()
|
{
|
}
|
|
public XLSX (File source) throws Exception
|
{
|
workbook = new XSSFWorkbook(new FileInputStream(source));
|
}
|
|
|
public byte[] save(String format) throws Exception
|
{
|
if ((format.equalsIgnoreCase("xlsx")) || (format.equalsIgnoreCase("xlsm")))
|
{
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
workbook.write(bos);
|
byte[] xlsx = bos.toByteArray();
|
bos.close();
|
return xlsx;
|
}
|
|
if (format.equalsIgnoreCase("pdf"))
|
{
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
workbook.write(bos);
|
byte[] xlsx = bos.toByteArray();
|
bos.close();
|
return toPDF(xlsx);
|
}
|
|
if (format.equalsIgnoreCase("csv"))
|
{
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
workbook.write(bos);
|
byte[] xlsx = bos.toByteArray();
|
bos.close();
|
return toSCV(xlsx);
|
}
|
|
throw new Exception("Unknow format");
|
|
}
|
|
|
|
public byte[] saveAndClose(String format) throws Exception
|
{
|
byte[] result = save(format);
|
close();
|
return result;
|
}
|
|
|
|
|
public void close ()
|
{
|
try
|
{
|
workbook.close();
|
}
|
catch (Exception e)
|
{
|
}
|
}
|
|
|
|
|
|
public byte[] save(SXSSFWorkbook sxworkbook, String format) throws Exception
|
{
|
if ((format.equalsIgnoreCase("xlsx")) || (format.equalsIgnoreCase("xlsm")))
|
{
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
sxworkbook.write(bos);
|
byte[] xlsx = bos.toByteArray();
|
bos.close();
|
return xlsx;
|
}
|
|
if (format.equalsIgnoreCase("pdf"))
|
{
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
sxworkbook.write(bos);
|
byte[] xlsx = bos.toByteArray();
|
bos.close();
|
return toPDF(xlsx);
|
}
|
|
if (format.equalsIgnoreCase("csv"))
|
{
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
sxworkbook.write(bos);
|
byte[] xlsx = bos.toByteArray();
|
bos.close();
|
return toSCV(xlsx);
|
}
|
|
throw new Exception("Unknow format");
|
|
}
|
|
|
|
public byte[] saveAndClose(SXSSFWorkbook sxworkbook, String format) throws Exception
|
{
|
byte[] result = save(sxworkbook, format);
|
close(sxworkbook);
|
return result;
|
}
|
|
|
|
|
public void close (SXSSFWorkbook sxworkbook)
|
{
|
close();
|
|
try
|
{
|
sxworkbook.close();
|
}
|
catch (Exception e)
|
{
|
}
|
}
|
|
|
public XSSFCell getXSSFCell(XSSFSheet xssfsheet, String reference)
|
{
|
CellReference cellReference = new CellReference(reference);
|
XSSFRow row = xssfsheet.getRow(cellReference.getRow());
|
XSSFCell cell = row.getCell(cellReference.getCol());
|
return cell;
|
}
|
|
|
|
|
|
|
// La primera columna e 1 y no 0
|
|
private char[] chars = new char[] {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
|
|
protected String columnName (int index)
|
{
|
index -= 1;
|
|
int quotient = index / 26;
|
if (quotient > 0)
|
{
|
return columnName(quotient) + chars[index % 26];
|
}
|
else
|
{
|
return "" + chars[index % 26];
|
}
|
}
|
|
|
|
public void createRow (XSSFSheet hoja, int numeroFilaReferencia, int numeroFila) throws Exception
|
{
|
if (numeroFilaReferencia == numeroFila) return;
|
XSSFRow xssfrowReferencia = hoja.getRow(numeroFilaReferencia);
|
XSSFRow xssfrow = hoja.createRow(numeroFila);
|
xssfrow.setHeight(xssfrowReferencia.getHeight());
|
}
|
|
public void createRow (XSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas) throws Exception
|
{
|
createRow(sheet, numeroFilaReferencia, numeroFila, numeroColumnas, false);
|
}
|
|
|
public void createRow (XSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas, boolean copycontent) throws Exception
|
{
|
// Creamos la fila
|
|
if (numeroFilaReferencia == numeroFila) return;
|
XSSFRow sourceRow = sheet.getRow(numeroFilaReferencia);
|
XSSFRow destinationRow = sheet.createRow(numeroFila);
|
destinationRow.setHeight(sourceRow.getHeight());
|
|
for (int i=0; i<numeroColumnas; i++)
|
{
|
XSSFCell celda = destinationRow.createCell(i);
|
|
if (sourceRow.getCell(i) != null)
|
{
|
celda.setCellStyle(sourceRow.getCell(i).getCellStyle());
|
|
if (copycontent == true)
|
{
|
copyValue(sourceRow.getCell(i), celda);
|
}
|
}
|
}
|
|
// If there are are any merged regions in the source row, copy to new row
|
|
for (CellRangeAddress cellRangeAddress : sheet.getMergedRegions())
|
{
|
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum())
|
{
|
try
|
{
|
CellRangeAddress newCellRangeAddress = new CellRangeAddress
|
(
|
destinationRow.getRowNum(),
|
(destinationRow.getRowNum() +
|
(cellRangeAddress.getFirstRow() -
|
cellRangeAddress.getLastRow())),
|
cellRangeAddress.getFirstColumn(),
|
cellRangeAddress.getLastColumn()
|
);
|
|
sheet.addMergedRegion(newCellRangeAddress);
|
}
|
catch (Exception e)
|
{
|
}
|
}
|
}
|
}
|
|
|
public void copyValue (XSSFCell source, XSSFCell destination) throws Exception
|
{
|
if (source == null) return;
|
if (destination == null) return;
|
|
if (source.getStringCellValue().isEmpty() == false)
|
{
|
switch(source.getCellTypeEnum())
|
{
|
case STRING:
|
destination.setCellValue(source.getStringCellValue());
|
break;
|
case NUMERIC:
|
destination.setCellValue(source.getNumericCellValue());
|
break;
|
case BLANK:
|
destination.setCellType(CellType.BLANK);
|
break;
|
case BOOLEAN:
|
destination.setCellValue(source.getBooleanCellValue());
|
break;
|
case ERROR:
|
destination.setCellErrorValue(source.getErrorCellValue());
|
break;
|
case FORMULA:
|
destination.setCellFormula(source.getCellFormula());
|
break;
|
default:
|
break;
|
}
|
}
|
}
|
|
|
|
public void createRow (XSSFSheet sheet, SXSSFSheet sxsheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas) throws Exception
|
{
|
createRow(sheet, sxsheet, numeroFilaReferencia, numeroFila, numeroColumnas, false);
|
}
|
|
|
public void createRow (XSSFSheet sheet, SXSSFSheet sxsheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas, boolean copycontent) throws Exception
|
{
|
// Creamos la fila
|
|
XSSFRow sourceRow = sheet.getRow(numeroFilaReferencia);
|
SXSSFRow destinationRow = sxsheet.createRow(numeroFila);
|
destinationRow.setHeight(sourceRow.getHeight());
|
|
for (int i=0; i<numeroColumnas; i++)
|
{
|
SXSSFCell celda = destinationRow.createCell(i);
|
|
if (sourceRow.getCell(i) != null)
|
{
|
celda.setCellStyle(sourceRow.getCell(i).getCellStyle());
|
|
if (copycontent == true)
|
{
|
copyValue(sourceRow.getCell(i), celda);
|
}
|
}
|
}
|
|
// If there are are any merged regions in the source row, copy to new row
|
|
for (CellRangeAddress cellRangeAddress : sheet.getMergedRegions())
|
{
|
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum())
|
{
|
try
|
{
|
CellRangeAddress newCellRangeAddress = new CellRangeAddress
|
(
|
destinationRow.getRowNum(),
|
(destinationRow.getRowNum() +
|
(cellRangeAddress.getFirstRow() -
|
cellRangeAddress.getLastRow())),
|
cellRangeAddress.getFirstColumn(),
|
cellRangeAddress.getLastColumn()
|
);
|
|
sheet.addMergedRegion(newCellRangeAddress);
|
}
|
catch (Exception e)
|
{
|
}
|
}
|
}
|
}
|
|
|
public void copyValue (XSSFCell source, SXSSFCell destination) throws Exception
|
{
|
if (source == null) return;
|
if (destination == null) return;
|
|
if (source.getStringCellValue().isEmpty() == false)
|
{
|
switch(source.getCellTypeEnum())
|
{
|
case STRING:
|
destination.setCellValue(source.getStringCellValue());
|
break;
|
case NUMERIC:
|
destination.setCellValue(source.getNumericCellValue());
|
break;
|
case BLANK:
|
destination.setCellType(CellType.BLANK);
|
break;
|
case BOOLEAN:
|
destination.setCellValue(source.getBooleanCellValue());
|
break;
|
case ERROR:
|
destination.setCellErrorValue(source.getErrorCellValue());
|
break;
|
case FORMULA:
|
destination.setCellFormula(source.getCellFormula());
|
break;
|
default:
|
break;
|
}
|
}
|
}
|
|
|
|
public String getCellValueAsText (XSSFCell cell)
|
{
|
try
|
{
|
switch(cell.getCellTypeEnum())
|
{
|
case STRING: return cell.getStringCellValue();
|
case NUMERIC: return "" + (int)cell.getNumericCellValue();
|
case BOOLEAN: return "" + cell.getBooleanCellValue();
|
case FORMULA: return "" + cell.getCellFormula();
|
}
|
}
|
catch (Exception e)
|
{
|
}
|
|
return "";
|
}
|
|
|
|
|
public int getColumnWidth (XSSFSheet sheet, int col1, int col2) throws Exception
|
{
|
int result = 0;
|
|
for (int i=col1; i<=col2; i++)
|
{
|
result = result + sheet.getColumnWidth(i);
|
}
|
|
return result;
|
}
|
|
|
public float getColumnWidthInPixels (XSSFSheet sheet, int col1, int col2) throws Exception
|
{
|
float result = 0;
|
|
for (int i=col1; i<=col2; i++)
|
{
|
result = result + sheet.getColumnWidthInPixels(i);
|
}
|
|
return result;
|
}
|
|
|
|
|
public void createRow (SXSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas) throws Exception
|
{
|
createRow(sheet, numeroFilaReferencia, numeroFila, numeroColumnas, false);
|
}
|
|
|
public void createRow (SXSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas, boolean copycontent) throws Exception
|
{
|
// Creamos la fila
|
|
if (numeroFilaReferencia == numeroFila) return;
|
SXSSFRow sourceRow = sheet.getRow(numeroFilaReferencia);
|
SXSSFRow destinationRow = sheet.createRow(numeroFila);
|
destinationRow.setHeight(sourceRow.getHeight());
|
|
for (int i=0; i<numeroColumnas; i++)
|
{
|
SXSSFCell celda = destinationRow.createCell(i);
|
|
if (sourceRow.getCell(i) != null)
|
{
|
celda.setCellStyle(sourceRow.getCell(i).getCellStyle());
|
|
if (copycontent == true)
|
{
|
copyValue(sourceRow.getCell(i), celda);
|
}
|
}
|
}
|
|
// If there are are any merged regions in the source row, copy to new row
|
|
for (CellRangeAddress cellRangeAddress : sheet.getMergedRegions())
|
{
|
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum())
|
{
|
try
|
{
|
CellRangeAddress newCellRangeAddress = new CellRangeAddress
|
(
|
destinationRow.getRowNum(),
|
(destinationRow.getRowNum() +
|
(cellRangeAddress.getFirstRow() -
|
cellRangeAddress.getLastRow())),
|
cellRangeAddress.getFirstColumn(),
|
cellRangeAddress.getLastColumn()
|
);
|
|
sheet.addMergedRegion(newCellRangeAddress);
|
}
|
catch (Exception e)
|
{
|
}
|
}
|
}
|
}
|
|
|
public void copyValue (SXSSFCell source, SXSSFCell destination) throws Exception
|
{
|
if (source == null) return;
|
if (destination == null) return;
|
|
if (source.getStringCellValue().isEmpty() == false)
|
{
|
switch(source.getCellTypeEnum())
|
{
|
case STRING:
|
destination.setCellValue(source.getStringCellValue());
|
break;
|
case NUMERIC:
|
destination.setCellValue(source.getNumericCellValue());
|
break;
|
case BLANK:
|
destination.setCellType(CellType.BLANK);
|
break;
|
case BOOLEAN:
|
destination.setCellValue(source.getBooleanCellValue());
|
break;
|
case ERROR:
|
destination.setCellErrorValue(source.getErrorCellValue());
|
break;
|
case FORMULA:
|
destination.setCellFormula(source.getCellFormula());
|
break;
|
default:
|
break;
|
}
|
}
|
}
|
|
|
public String getDate(long timestamp)
|
{
|
SimpleDateFormat ISO8601DATEFORMAT = new SimpleDateFormat(Shared.getMessage("yyyy-MM-dd HH:mm:ss"));
|
ISO8601DATEFORMAT.setTimeZone(TimeZone.getDefault());
|
return ISO8601DATEFORMAT.format(timestamp);
|
}
|
|
|
|
|
|
|
|
public int getNumberOfDays(long timestamp1, long timestamp2)
|
{
|
Calendar calendar1 = Calendar.getInstance();
|
calendar1.setTimeInMillis(timestamp1);
|
calendar1.set(Calendar.HOUR_OF_DAY,0);
|
calendar1.set(Calendar.MINUTE,0);
|
calendar1.set(Calendar.SECOND,0);
|
calendar1.set(Calendar.MILLISECOND,0);
|
|
Calendar calendar2 = Calendar.getInstance();
|
calendar2.setTimeInMillis(timestamp2);
|
calendar2.set(Calendar.HOUR_OF_DAY,0);
|
calendar2.set(Calendar.MINUTE,0);
|
calendar2.set(Calendar.SECOND,0);
|
calendar2.set(Calendar.MILLISECOND,0);
|
|
int numberOfDays = 0;
|
|
do
|
{
|
numberOfDays = numberOfDays + 1;
|
calendar1.add(Calendar.DATE, 1);
|
}
|
while(calendar1.getTimeInMillis() < calendar2.getTimeInMillis());
|
|
|
return numberOfDays;
|
}
|
|
|
|
public void setBorder(XSSFSheet xssfsheet, CellRangeAddress cellRangeAddress, BorderStyle borderStyle)
|
{
|
RegionUtil.setBorderTop(borderStyle, cellRangeAddress, xssfsheet);
|
RegionUtil.setBorderLeft(borderStyle, cellRangeAddress, xssfsheet);
|
RegionUtil.setBorderBottom(borderStyle, cellRangeAddress, xssfsheet);
|
RegionUtil.setBorderRight(borderStyle, cellRangeAddress, xssfsheet);
|
}
|
|
|
|
public void setBorder(SXSSFSheet sxssfsheet, CellRangeAddress cellRangeAddress, BorderStyle borderStyle)
|
{
|
RegionUtil.setBorderTop(borderStyle, cellRangeAddress, sxssfsheet);
|
RegionUtil.setBorderLeft(borderStyle, cellRangeAddress, sxssfsheet);
|
RegionUtil.setBorderBottom(borderStyle, cellRangeAddress, sxssfsheet);
|
RegionUtil.setBorderRight(borderStyle, cellRangeAddress, sxssfsheet);
|
}
|
|
|
|
|
public byte[] toPDF(byte[] xlsx) throws ServerException, Exception
|
{
|
try
|
{
|
// Initialise
|
String oooExeFolder = ((Configuration)Shared.configuration).detail.libreOfficebinaryPath;
|
XComponentContext xContext = BootstrapSocketConnector.bootstrap(oooExeFolder);
|
XMultiComponentFactory xMCF = xContext.getServiceManager();
|
Object oDesktop = xMCF.createInstanceWithContext("com.sun.star.frame.Desktop", xContext);
|
XDesktop xDesktop = (XDesktop) UnoRuntime.queryInterface(XDesktop.class, oDesktop);
|
XComponentLoader xCompLoader = (XComponentLoader)UnoRuntime.queryInterface(com.sun.star.frame.XComponentLoader.class, xDesktop);
|
|
PropertyValue[] propertyValues = new PropertyValue[2];
|
propertyValues[0] = new PropertyValue();
|
propertyValues[0].Name = "Hidden";
|
propertyValues[0].Value = new Boolean(true);
|
propertyValues[1] = new PropertyValue();
|
propertyValues[1].Name = "InputStream";
|
propertyValues[1].Value = new ByteArrayToXInputStreamAdapter(xlsx);
|
XComponent xComp = xCompLoader.loadComponentFromURL("private:stream", "_blank", 0, propertyValues);
|
|
|
// save as a PDF
|
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
XStorable xStorable = (XStorable) UnoRuntime.queryInterface(XStorable.class, xComp);
|
propertyValues = new PropertyValue[3];
|
propertyValues[0] = new PropertyValue();
|
propertyValues[0].Name = "Overwrite";
|
propertyValues[0].Value = new Boolean(true);
|
propertyValues[1] = new PropertyValue();
|
propertyValues[1].Name = "FilterName";
|
propertyValues[1].Value = "writer_pdf_Export";
|
propertyValues[2] = new PropertyValue();
|
propertyValues[2].Name = "OutputStream";
|
propertyValues[2].Value = new OutputStreamToXOutputStreamAdapter(bos);
|
xStorable.storeToURL("private:stream", propertyValues);
|
|
// shutdown
|
|
xDesktop.terminate();
|
|
// Result
|
|
byte[] pdf = bos.toByteArray();
|
bos.close();
|
return pdf;
|
}
|
catch (Exception e)
|
{
|
throw new ServerException(Shared.getMessage(language, "Error generating pdf"));
|
}
|
}
|
|
|
|
|
public byte[] toSCV (byte[] xlsx) throws ServerException, Exception
|
{
|
try
|
{
|
XSSFFormulaEvaluator fe = workbook.getCreationHelper().createFormulaEvaluator();
|
DataFormatter formatter = new DataFormatter();
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
PrintStream out = new PrintStream(bos, true, "UTF-8");
|
byte[] bom = {(byte)0xEF, (byte)0xBB, (byte)0xBF};
|
{
|
for (int i=0; i<workbook.getNumberOfSheets(); i++)
|
{
|
XSSFSheet sheet = workbook.getSheetAt(i);
|
|
for (int r = 0, rn = sheet.getLastRowNum() ; r <= rn ; r++)
|
{
|
XSSFRow row = sheet.getRow(r);
|
if ( row == null ) { out.println(';'); continue; }
|
boolean firstCell = true;
|
for (int c = 0, cn = row.getLastCellNum() ; c < cn ; c++)
|
{
|
XSSFCell cell = row.getCell(c, XSSFRow.MissingCellPolicy.RETURN_BLANK_AS_NULL);
|
|
if (!firstCell) out.print(';');
|
|
try
|
{
|
if (cell != null)
|
{
|
if (cell.getCellTypeEnum() == CellType.BLANK)
|
{
|
out.print("");
|
}
|
else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
|
{
|
out.print(cell.getBooleanCellValue());
|
}
|
else if (cell.getCellTypeEnum() == CellType.ERROR)
|
{
|
out.print("");
|
}
|
else if (cell.getCellTypeEnum() == CellType.FORMULA)
|
{
|
out.print(formatter.formatCellValue(fe.evaluateInCell(cell)));
|
}
|
else if (cell.getCellTypeEnum() == CellType.NUMERIC)
|
{
|
out.print(cell.getNumericCellValue());
|
}
|
else if (cell.getCellTypeEnum() == CellType.STRING)
|
{
|
out.print(cell.getStringCellValue());
|
}
|
else if (cell.getCellTypeEnum() == CellType._NONE)
|
{
|
out.print("");
|
}
|
else
|
{
|
out.print("");
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
}
|
firstCell = false;
|
}
|
out.println();
|
}
|
}
|
}
|
|
out.close();
|
bos.close();
|
return bos.toByteArray();
|
}
|
catch (Exception e)
|
{
|
throw new ServerException(Shared.getMessage(language, "Error generating csv"));
|
}
|
}
|
|
|
|
|
static private Pattern rxquote = Pattern.compile("\"");
|
|
static private String encodeValue(String value)
|
{
|
boolean needQuotes = false;
|
if (value.indexOf(',') != -1 || value.indexOf('"') != -1 || value.indexOf('\n') != -1 || value.indexOf('\r') != -1)
|
{
|
needQuotes = true;
|
}
|
Matcher m = rxquote.matcher(value);
|
if ( m.find() ) needQuotes = true; value = m.replaceAll("\"\"");
|
if ( needQuotes ) return "\"" + value + "\"";
|
else return value;
|
|
}
|
|
|
}
|
