/**
* Create a new row within the sheet and return the high level representation
*
* @param rownum row number
* @return high level Row object representing a row in the sheet
* @throws IllegalArgumentException If the max. number of rows is exceeded or a rownum is provided
* where the row is already flushed to disk.
* @see #removeRow(Row)
*/
public Row createRow(int rownum) {
int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
if (rownum < 0 || rownum > maxrow) {
throw new IllegalArgumentException(
"Invalid row number (" + rownum + ") outside allowable range (0.." + maxrow + ")");
}
// attempt to overwrite a row that is already flushed to disk
if (rownum <= _writer.getLastFlushedRow()) {
throw new IllegalArgumentException(
"Attempting to write a row["
+ rownum
+ "] "
+ "in the range [0,"
+ _writer.getLastFlushedRow()
+ "] that is already written to disk.");
}
// attempt to overwrite a existing row in the input template
if (_sh.getPhysicalNumberOfRows() > 0 && rownum <= _sh.getLastRowNum()) {
throw new IllegalArgumentException(
"Attempting to write a row["
+ rownum
+ "] "
+ "in the range [0,"
+ _sh.getLastRowNum()
+ "] that is already written to disk.");
}
// Make the initial allocation as big as the row above.
Row previousRow = rownum > 0 ? getRow(rownum - 1) : null;
int initialAllocationSize = 0;
// have previous row in memory -> take that value.
if (previousRow != null) initialAllocationSize = previousRow.getLastCellNum();
// are we called after a flush(0)? If yes, ask the writer for the value.
if (initialAllocationSize <= 0 && _writer.getNumberOfFlushedRows() > 0)
initialAllocationSize = _writer.getNumberOfCellsOfLastFlushedRow();
// default to 10 on the first row.
if (initialAllocationSize <= 0) initialAllocationSize = 10;
SXSSFRow newRow = new SXSSFRow(this, initialAllocationSize);
_rows.put(new Integer(rownum), newRow);
if (_randomAccessWindowSize >= 0 && _rows.size() > _randomAccessWindowSize) {
try {
flushRows(_randomAccessWindowSize);
} catch (IOException ioe) {
throw new RuntimeException(ioe);
}
}
return newRow;
}
public String getSheetInfo() {
StringBuilder info = new StringBuilder();
info.append("File: " + fileExcel.getAbsolutePath() + "\n");
info.append("Aktuelle Tabelle: " + xssfSheet.getSheetName() + "\n");
info.append(getColumnCountFromSheet() + " Spalten\n");
info.append(xssfSheet.getLastRowNum() + " Datenzeilen");
return info.toString();
}
// returns the row count in a sheet
public int getRowCount(String sheetName) {
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return 0;
else {
sheet = workBook.getSheetAt(index);
int number = sheet.getLastRowNum() + 1;
return number;
}
}
public static Object[][] getTableArray(String FilePath, String SheetName) throws Exception {
String[][] tabArray = null;
try {
FileInputStream ExcelFile = new FileInputStream(FilePath);
// Access the required test data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
int startRow = 1;
int startCol = 1;
int ci, cj;
int totalRows = ExcelWSheet.getLastRowNum();
// you can write a function as well to get Column count
int totalCols = 2;
tabArray = new String[totalRows][totalCols];
ci = 0;
for (int i = startRow; i <= totalRows; i++, ci++) {
cj = 0;
for (int j = startCol; j <= totalCols; j++, cj++) {
tabArray[ci][cj] = getCellData(i, j);
System.out.println(tabArray[ci][cj]);
}
}
} catch (FileNotFoundException e) {
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
} catch (IOException e) {
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
}
return (tabArray);
}
public static int getRowUsed() throws Exception {
try {
int RowCount = ExcelWSheet.getLastRowNum();
// Log.info("Total number of Row used return as < " + RowCount + " >.");
return RowCount;
} catch (Exception e) {
Log.error("Class ExcelUtil | Method getRowUsed | Exception desc : " + e.getMessage());
// System.out.println(e.getMessage());
throw (e);
}
}
private int getColumnCountFromSheet() {
int lastRow = xssfSheet.getLastRowNum();
short lastCol = 0;
short lastCellInRow;
XSSFRow row = null;
for (int i = 0; i < lastRow; i++) {
row = xssfSheet.getRow(i);
lastCellInRow = row.getLastCellNum();
if (lastCellInRow > lastCol) {
lastCol = lastCellInRow;
}
}
return lastCol;
}
private void applyConditionalFormattingToCopiedRows(XSSFSheet sheet, int startRow) {
final int lastRow = sheet.getLastRowNum();
XSSFSheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
// Go from end to start because we will be removing them
for (int i = formatting.getNumConditionalFormattings() - 1; i >= 0; i--) {
// Get conditional formatting
XSSFConditionalFormatting format = formatting.getConditionalFormattingAt(i);
// Apply conditional formatting to new range
CellRangeAddress[] ranges =
computeNewFormattingRanges(format.getFormattingRanges(), startRow, lastRow);
applyRulesToRanges(formatting, format, ranges);
// remove previous version of the conditional formatting
formatting.removeConditionalFormatting(i);
}
}
/**
* @param newSheet the sheet to create from the copy.
* @param sheet the sheet to copy.
* @param copyStyle true copy the style.
*/
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
int maxColumnNum = 0;
Map<Integer, XSSFCellStyle> styleMap =
(copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i);
if (srcRow != null) {
XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public void fromExcelToDB() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost/excel", "root", "21092012artem");
con.setAutoCommit(false);
PreparedStatement pstm = null;
File file = new File("D://file.xlsx");
if (!file.exists()) {}
InputStream input = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(input);
XSSFSheet sheet = wb.getSheetAt(0);
Row row;
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
int id = (int) row.getCell(0).getNumericCellValue();
String name = row.getCell(1).getStringCellValue();
String address = row.getCell(2).getStringCellValue();
String sql = "INSERT INTO excel VALUES('" + id + "','" + name + "','" + address + "')";
pstm = (PreparedStatement) con.prepareStatement(sql);
pstm.execute();
System.out.println("Import rows " + i);
}
con.commit();
pstm.close();
con.close();
input.close();
System.out.println("Success import excel to mysql table");
} catch (ClassNotFoundException e) {
System.out.println(e);
} catch (SQLException ex) {
System.out.println(ex);
} catch (IOException ioe) {
System.out.println(ioe);
}
}
private static void extractGeoJSONFiles(FileInputStream in, String outPath) throws IOException {
outPath += "geojson/";
XSSFWorkbook workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheet("scenarios");
int numRows = sheet.getLastRowNum() + 1;
for (int i = 0; i < numRows; i++) {
XSSFRow row = sheet.getRow(i);
String userId = ((int) row.getCell(2).getNumericCellValue()) + "";
String scenarioId = row.getCell(5).getStringCellValue();
String geoJSON = row.getCell(6).getStringCellValue();
String crsString =
"\"crs\": { \"type\": \"name\", \"properties\": { \"name\": \"urn:ogc:def:crs:EPSG::3395\" } },";
geoJSON = geoJSON.replaceFirst(",", ",\n" + crsString + "\n");
LOG.info(geoJSON);
String filename = userId + "_" + scenarioId + ".geojson";
String subdir = scenarioId.contains("-1") ? "/1/" : "/2/";
writeToFile(geoJSON, filename, outPath + subdir);
LOG.info("written geoJSON to " + outPath + filename);
}
workbook.close();
}
public List<String> getAttributes(MultipartFile attributeReport, String classId) {
List<String> attributes = new ArrayList<String>();
try {
XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(attributeReport.getInputStream());
XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
for (int i = 2; i < worksheetIn.getLastRowNum(); i++) {
XSSFRow rowIn = worksheetIn.getRow(i);
if (XSSFCell.CELL_TYPE_NUMERIC == rowIn.getCell(1).getCellType()) {
if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
attributes.add(
rowIn.getCell(2).getStringCellValue()
+ "###"
+ rowIn.getCell(7).getStringCellValue());
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return attributes;
}
public static void main(String[] args) {
File f = new File("D:\\data.xlsx");
try {
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(f));
if (workbook != null) {
XSSFSheet sheet = workbook.getSheetAt(0);
for (int i = 0; i < sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
String bigPic = row.getCell(0).toString();
System.out.println(bigPic);
for (int j = 0; j < row.getLastCellNum(); j++) {
System.out.print("," + row.getCell(j));
}
System.out.println("\n");
}
System.out.println("ok");
}
} catch (Exception e) {
e.printStackTrace();
}
}
public List<XSSFCell> writeStyle(MultipartFile fileIn, String classId) {
List<XSSFCell> attributeCell = new ArrayList<XSSFCell>();
try {
XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
for (int i = 1; i < worksheetIn.getLastRowNum(); i++) {
XSSFRow rowIn = worksheetIn.getRow(i);
if (XSSFCell.CELL_TYPE_NUMERIC == rowIn.getCell(7).getCellType()) {
if (rowIn.getCell(7).getNumericCellValue() == Integer.parseInt(classId)) {
attributeCell.add(rowIn.getCell(14));
int j = 20;
for (; j < 44; ) {
attributeCell.add(rowIn.getCell(j));
j += 2;
}
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return attributeCell;
}
public static boolean appendArray2XLSXFile(
String fileName, List<String[]> arry, boolean hasHead, int indexKey) throws Exception {
int rowNum = arry.size();
if (rowNum == 0) {
System.err.println("No input data!");
return false;
}
XSSFWorkbook wb;
XSSFSheet sheet1;
File file = new File(fileName);
if (!file.exists()) {
file.getParentFile().mkdirs();
wb = new XSSFWorkbook();
sheet1 = wb.createSheet("Data");
} else {
if (hasHead) {
// I hope this is a new file this time
file.delete();
wb = new XSSFWorkbook();
sheet1 = wb.createSheet("Data");
} else {
FileInputStream inFile = new FileInputStream(fileName);
wb = new XSSFWorkbook(inFile);
sheet1 = wb.getSheetAt(0);
inFile.close();
}
}
String[] titleNames = null;
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int overallRowIndex = 0;
if (!hasHead) {
overallRowIndex = sheet1.getLastRowNum() + 1;
rowNum = rowNum + overallRowIndex;
}
int currRowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(overallRowIndex);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
if (hasHead && titleNames != null) {
titleNames = (String[]) arry.get(0);
XSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
overallRowIndex++;
currRowIndex++;
}
for (int i = overallRowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = arry.get(currRowIndex);
columnNum = line.length;
if (indexKey >= 0) {
if (lastKey == null && line[indexKey] != null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey != null && line[indexKey] == null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey == null && line[indexKey] == null) {
} else {
if (!lastKey.equals(line[indexKey])) {
switcher *= -1;
lastKey = line[indexKey];
}
}
} else {
switcher = 1;
}
// System.out.println(cells1[0]);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
currRowIndex++;
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public XSSFWorkbook writeCTGI(
XSSFWorkbook cTGWorkbook,
MultipartFile fileIn,
String classId,
MultipartFile staplesMasterStyleGuide,
MultipartFile attributeReport) {
try {
List<XSSFCell> attributeCell = writeStyle(staplesMasterStyleGuide, classId);
List<String> attributes = getAttributes(attributeReport, classId);
XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
XSSFSheet worksheetOut = cTGWorkbook.getSheetAt(0);
int i = 1;
int j = 4;
XSSFCell cell = null;
for (i = 1; i < worksheetIn.getLastRowNum(); i++) {
XSSFRow rowIn = worksheetIn.getRow(i);
XSSFRow rowOut = worksheetOut.createRow(j);
if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
XSSFCell cell1 = rowOut.createCell(13);
if (rowIn.getCell(2) != null) cell1.setCellValue(rowIn.getCell(2).getStringCellValue());
XSSFCell cell2 = rowOut.createCell(15);
cell2.setCellValue(classId);
XSSFCell cell3 = rowOut.createCell(19);
if (rowIn.getCell(3) != null) cell3.setCellValue(rowIn.getCell(3).getStringCellValue());
XSSFCell cell4 = rowOut.createCell(20);
if (rowIn.getCell(0) != null) cell4.setCellValue(rowIn.getCell(0).getStringCellValue());
XSSFCell cell5 = rowOut.createCell(24);
if (rowIn.getCell(3) != null) cell5.setCellValue(rowIn.getCell(3).getStringCellValue());
XSSFCell cell6 = rowOut.createCell(179);
if (rowIn.getCell(5) != null) cell6.setCellValue(rowIn.getCell(5).getNumericCellValue());
XSSFCell cell7 = rowOut.createCell(26);
cell7 = attributeCell.get(0);
int z = 1;
for (int k = 36; k < 48; k++) {
cell = rowOut.createCell(k);
cell.setCellValue((attributeCell.get(z)).getStringCellValue());
z++;
}
int counter = 1;
int cellCount = 63;
for (String attribute : attributes) {
if (counter <= 50) {
String lable = attribute.split("###")[0];
String value = attribute.split("###")[1];
cell = rowOut.createCell(cellCount);
cell.setCellValue(lable);
cell = rowOut.createCell(cellCount + 1);
cell.setCellValue(value);
counter++;
cellCount += 2;
}
}
}
j++;
}
} catch (IOException e) {
e.printStackTrace();
}
return cTGWorkbook;
}
public static int getLastRowNum() {
return ExcelWSheet.getLastRowNum();
}
private void renderCell(
BandElement bandElement,
String bandName,
Object value,
int gridRow,
int sheetRow,
int sheetColumn,
int rowSpan,
int colSpan,
boolean image) {
if (bandElement instanceof ReportBandElement) {
colSpan = 1;
}
XSSFCellStyle cellStyle =
buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan);
// if we have a subreport on the current grid row we have to take care of the sheetColumn
if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
&& (gridRow == prevSubreportFirstRow)
&& (prevSubreportLastColumn != -1)) {
sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn;
}
XSSFCell c = xlsRow.createCell(sheetColumn);
if (image) {
if ((value == null) || "".equals(value)) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_FOUND));
} else {
try {
ImageBandElement ibe = (ImageBandElement) bandElement;
byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight());
XSSFClientAnchor anchor =
new XSSFClientAnchor(
0,
0,
0,
0,
(short) sheetColumn,
sheetRow,
(short) (sheetColumn + colSpan),
(sheetRow + rowSpan));
int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
// image is created over the cells, so if it's height is bigger we set the row height
short height = xlsRow.getHeight();
int realImageHeight = getRealImageSize((String) value)[1];
if (ibe.isScaled()) {
realImageHeight = ibe.getHeight();
}
short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
boolean doResize = false;
if (imageHeight > height) {
xlsRow.setHeight(imageHeight);
} else {
doResize = true;
}
Picture picture = patriarch.createPicture(anchor, index);
if (doResize) {
picture.resize();
}
anchor.setAnchorType(2);
} catch (Exception ex) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
}
}
if (cellStyle != null) {
c.setCellStyle(cellStyle);
}
} else {
if (bandElement instanceof HyperlinkBandElement) {
Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink();
XSSFHyperlink link = wb.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_URL);
link.setAddress(hyp.getUrl());
c.setHyperlink(link);
c.setCellValue(wb.getCreationHelper().createRichTextString(hyp.getText()));
c.setCellType(XSSFCell.CELL_TYPE_STRING);
} else if (bandElement instanceof ReportBandElement) {
Report report = ((ReportBandElement) bandElement).getReport();
ExporterBean eb = null;
try {
eb = getSubreportExporterBean(report, true);
XlsxExporter subExporter = new XlsxExporter(eb, cellStyle);
subExporter.export();
XSSFSheet subreportSheet = subExporter.getSubreportSheet();
if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
&& (gridRow == prevSubreportFirstRow)) {
// other subreports on the same header line after the first
sheetColumn = prevSubreportLastColumn;
sheetRow -= addedPageRows;
pageRow -= addedPageRows;
addedPageRows = 0;
} else {
addedPageRows = subreportSheet.getLastRowNum();
pageRow += addedPageRows;
// if subreport is not on the first column we merge all cells in the columns before,
// between the rows subreport occupies
if (sheetColumn > 0) {
for (int i = 0; i <= sheetColumn - 1; i++) {
CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i);
regions.add(new XlsxRegion(cra, null));
}
}
}
int cols = XlsxUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet);
addRegions(xlsSheet, subExporter.getSubreportRegions(), wb);
if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) {
prevSubreportFirstRow = gridRow;
prevSubreportFirstColumn = sheetColumn;
prevSubreportLastColumn = sheetColumn + cols;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if ((eb != null) && (eb.getResult() != null)) {
eb.getResult().close();
}
}
} else if (bandElement instanceof ImageColumnBandElement) {
try {
ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement;
String v = StringUtil.getValueAsString(value, null);
if (StringUtil.BLOB.equals(v)) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(StringUtil.BLOB));
} else {
byte[] imageD = StringUtil.decodeImage(v);
byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight());
XSSFClientAnchor anchor =
new XSSFClientAnchor(
0,
0,
0,
0,
(short) sheetColumn,
sheetRow,
(short) (sheetColumn + colSpan),
(sheetRow + rowSpan));
int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
// image is created over the cells, so if it's height is bigger we set the row height
short height = xlsRow.getHeight();
int realImageHeight = getRealImageSize(imageBytes)[1];
if (icbe.isScaled()) {
realImageHeight = icbe.getHeight();
}
short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
if (imageHeight > height) {
xlsRow.setHeight(imageHeight);
}
Picture picture = patriarch.createPicture(anchor, index);
picture.resize();
anchor.setAnchorType(2);
}
} catch (Exception e) {
e.printStackTrace();
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
}
} else {
if (value == null) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(""));
} else if (value instanceof Number) {
c.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue(((Number) value).doubleValue());
} else {
String pattern = null;
if (bandElement instanceof FieldBandElement) {
FieldBandElement fbe = (FieldBandElement) bandElement;
pattern = fbe.getPattern();
}
if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) {
Date date;
if (value instanceof java.sql.Date) {
date = new Date(((java.sql.Date) value).getTime());
} else {
date = (java.sql.Timestamp) value;
}
if (cellStyle != null) {
if (pattern == null) {
// use default pattern if none selected
Locale locale = Locale.getDefault();
pattern =
((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale))
.toPattern();
} else {
pattern = StringUtil.getI18nString(pattern, getReportLanguage());
}
cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern));
}
c.setCellValue(date);
} else {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
String text = StringUtil.getValueAsString(value, pattern);
if ((bandElement != null) && bandElement.isWrapText()) {
// try to interpret new line characters
// \\n is used here to be possible to add in designer grid cell with \n
if (text.contains("\\n")
|| text.contains("\n")
|| text.contains("\r")
|| text.contains("\r\n")) {
String crLf = Character.toString((char) 13) + Character.toString((char) 10);
int lines = countLines(text);
if (text.contains("\r\n")) {
text = text.replaceAll("\r\n", crLf);
} else {
text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf);
}
c.setCellValue(text);
cellStyle.setWrapText(true);
xlsRow.setHeightInPoints(lines * (cellStyle.getFont().getFontHeightInPoints() + 3));
} else {
c.setCellValue(wb.getCreationHelper().createRichTextString(text));
}
} else {
c.setCellValue(wb.getCreationHelper().createRichTextString(text));
}
}
}
}
if (cellStyle != null) {
if (bandElement != null) {
cellStyle.setRotation(bandElement.getTextRotation());
}
if (!(bandElement instanceof ReportBandElement)) {
c.setCellStyle(cellStyle);
}
}
if ((rowSpan > 1) || (colSpan > 1)) {
CellRangeAddress cra =
new CellRangeAddress(
sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1);
Border beBorder = bandElement.getBorder();
if (hasRowRenderConditions(bandElement, gridRow, value)) {
// for row render conditions we must keep the row border
beBorder = border;
}
regions.add(new XlsxRegion(cra, beBorder));
}
}
}
private void removeTemplateRows(XSSFSheet sheet, int startRow) {
sheet.shiftRows(startRow, sheet.getLastRowNum(), NB_HEADER_ROWS - startRow);
}
public String processFiles(
MultipartFile contentTemplateGenerator,
MultipartFile attributeReport,
MultipartFile staplesMasterStyleGuide)
throws IOException {
fileNameList = new ArrayList<>();
String status = "Processing Failed.";
System.out.println(contentTemplateGenerator);
System.out.println(attributeReport);
System.out.println(staplesMasterStyleGuide);
XSSFWorkbook cTGWorkbook = new XSSFWorkbook(contentTemplateGenerator.getInputStream());
XSSFSheet worksheet = cTGWorkbook.getSheetAt(0);
Set<String> uniqueClassId = new HashSet<String>();
DateFormat dateFormat = new SimpleDateFormat("MMddyyyy");
Date date = new Date();
String dateFinal = dateFormat.format(date);
System.out.println(dateFinal);
String vendor = "";
for (int i = 1; i < worksheet.getLastRowNum(); i++) {
XSSFRow row = worksheet.getRow(i);
uniqueClassId.add(row.getCell(1).getRawValue());
}
File file = null;
String fileName = null;
for (String s : uniqueClassId) {
for (int i = 1; i < worksheet.getLastRowNum(); i++) {
XSSFRow row = worksheet.getRow(i);
String vendortemp = row.getCell(6).getStringCellValue();
String classId = row.getCell(1).getRawValue();
if (s.equals(classId)) {
if (!vendor.equalsIgnoreCase(vendortemp)) {
fileName = SOURCE_FOLDER + "/" + s + "_" + vendortemp + "_" + dateFinal + ".xlsx";
file = new File(fileName);
if (file.createNewFile()) {
System.out.println("File is created:" + file.getName());
vendor = vendortemp;
}
}
}
}
vendor = "";
InputStream fin =
new FileInputStream(
"src/main/java/com/staples/product/template/Content_Smartsheet_Template- CTG 7 15 15.xlsx");
XSSFWorkbook cTGWorkbook_temp = new XSSFWorkbook(fin);
FileOutputStream fout = new FileOutputStream(file);
// cTGWorkbook_temp.write(fout);
XSSFWorkbook cTGWorkbookWithCTG =
writeCTGI(
cTGWorkbook_temp,
contentTemplateGenerator,
s,
staplesMasterStyleGuide,
attributeReport);
// XSSFWorkbook cTGWorkbookWithAttr=writeAttribute(cTGWorkbookWithCTG,
// staplesMasterStyleGuide, s);
// fout.close();
// fin.close();
// fout=new FileOutputStream(file);
cTGWorkbookWithCTG.write(fout);
fout.close();
System.out.println("Output File Format Created!!");
fileNameList.add(fileName);
}
if (fileNameList != null && !fileNameList.isEmpty()) {
zipIt(OUTPUT_ZIP_FILE);
}
status = OUTPUT_ZIP_FILE.split("/")[5];
System.out.println(status);
return status;
}
/** 导入Excel表格 */
@SuppressWarnings("unused")
public String intoDB() throws IOException {
String uploadPath = ServletActionContext.getServletContext().getRealPath("/upload");
isImpSuccess = false; // 判断数据导入是否成功
errCount = 0; // 判断导入数据模板是否对应
// 基于myFile创建一个文件输入流
InputStream is = new FileInputStream(myFile);
// 设置目标文件
File toFile = new File(uploadPath, this.getMyFileFileName());
String caseName = null; // 活动名称
String caseDesc = null; // 活动描述
Timestamp caseSt = null; // 活动开始时间
Timestamp caseEt = null; // 活动结束时间
String sysUserId = null; // 创建/修改用户ID
Timestamp sysDt = null; // 修改时间
Integer status = null; // 活动状态
String caseCode = null; // 活动编码
Double ratioNew = null; // 新款占比
Integer num = null; // 参与款数
Date date = new Date(); // 创建一个时间对象，获取到当前的时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 设置时间显示格式
String str = sdf.format(date); // 将当前时间格式化为需要的类型
sysDt = Timestamp.valueOf(str);
sysUserId = ParaCasePAction.getCurrentUserName();
/** 2007版的读取方法 */
int k = 0;
int flag = 0; // 指示指针所访问的位置
if (myFile != null) {
try {
Workbook workbook = WorkbookFactory.create(toFile);
intolist.clear();
// Workbook workbook = new XSSFWorkbook(is);//初始化workbook对象
for (int numSheets = 0;
numSheets < workbook.getNumberOfSheets();
numSheets++) { // 读取每一个sheet
if (null != workbook.getSheetAt(numSheets)) {
XSSFSheet aSheet = (XSSFSheet) workbook.getSheetAt(numSheets); // 定义Sheet对象
for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
// 进入当前sheet的行的循环
if (null != aSheet.getRow(rowNumOfSheet)) {
XSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 定义行，并赋值
for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {
// 读取rowNumOfSheet值所对应行的数据
XSSFCell xCell = aRow.getCell(cellNumOfRow); // 获得行的列数
// //获得列值
// System.out.println("type="+xCell.getCellType());
if (null != aRow.getCell(cellNumOfRow)) {
// 如果rowNumOfSheet的值为0，则读取表头，判断excel的格式和预定格式是否相符
if (rowNumOfSheet == 1) {
if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
/**
* 一下根据从Excel的各列命名是否符合要求：如下面匹配： 活动名称，活动描述，活动开始时间， 活动结束时间,操作用户,
* 修改时间,活动状态,活动编码,新款占比 ,参与款数,备注开始时间,备注结束时间
*/
if (cellNumOfRow == 0) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动名称")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 1) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动描述")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 2) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动开始时间")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 3) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动结束时间")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 4) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动状态")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 5) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动编码")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 6) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("新款占比")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 7) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("参与款数")) {
flag++;
} else {
errCount++;
}
}
}
} else {
// rowNumOfSheet != 0 即开始打印内容
// 获取excel中每列的值，并赋予相应的变量，如下的赋值的ID，name,sex,
// Dormitory,sept;
if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // 为数值型
System.out.println(
"===============进入XSSFCell .CELL_TYPE_NUMERIC模块============");
switch (cellNumOfRow) {
case 2:
Date St = (Date) xCell.getDateCellValue(); // 对日期处理
caseSt = new Timestamp(St.getTime());
break;
case 3:
Date Et = (Date) xCell.getDateCellValue(); // 对日期处理
caseEt = new Timestamp(Et.getTime());
break;
case 4:
status = (int) xCell.getNumericCellValue();
break;
case 6:
ratioNew = xCell.getNumericCellValue();
break;
case 7:
num = (int) xCell.getNumericCellValue();
break;
}
} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { // 为字符串型
System.out.println(
"===============进入XSSFCell .CELL_TYPE_STRING模块============");
switch (cellNumOfRow) {
case 0:
caseName =
xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim();
break;
case 1:
caseDesc =
xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim();
break;
case 5:
caseCode =
xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim();
break;
}
} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
System.out.println(
"提示：在Sheet"
+ (numSheets + 1)
+ "中的第"
+ (rowNumOfSheet + 1)
+ "行的第"
+ (cellNumOfRow + 1)
+ "列的值为空，请查看核对是否符合约定要求");
switch (cellNumOfRow) {
case 0:
caseName = "";
break;
case 1:
caseDesc = "";
break;
case 2:
caseSt = null;
break;
case 3:
caseEt = null;
break;
case 4:
status = null;
break;
case 5:
caseCode = "";
break;
}
}
}
}
}
// 判断各个元素被赋值,如果放入数据库，就直接使用数据的插入的函数就可以了。
if (aRow.getRowNum() > 1) {
ParaDt pd = new ParaDt();
pd.setCaseName(caseName);
pd.setCaseDesc(caseDesc);
pd.setCaseEt(caseEt);
pd.setCaseSt(caseSt);
pd.setSysUserId(sysUserId);
pd.setSysDt(sysDt);
pd.setStatus(status);
pd.setCaseCode(caseCode);
pd.setRatioNew(ratioNew);
pd.setNum(num);
pd.setCaseCode(caseCode);
intolist.add(pd);
}
} // 获得一行，即读取每一行
}
// 读取每一个sheet
}
}
refreshList = "paraCaseDtgetParaDtAll";
titleName = "营销活动实例";
if (errCount > 0) {
msg = "导入数据与模板不符，导入失败！";
} else {
// 调用sever方法
isImpSuccess = paraDtService.addOneBoat(intolist, 500);
if (isImpSuccess) {
msg = "营销活动实例导入成功！";
} else {
msg = "营销活动实例导入失败！";
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
return "importExcel";
}
private String createUpdate() {
String table = textField.getText().trim();
if (table.equals("") || table == null) {
return "";
}
int iRows = xssfSheet.getLastRowNum();
int iCol = getColumnCountFromSheet();
StringBuilder sbTotal = new StringBuilder();
StringBuilder sbStmt = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
StringBuilder sbSet = new StringBuilder();
List<String> listWheres = new ArrayList<String>();
List<String> listSets = new ArrayList<String>();
Row currRow;
for (int i = 1; i <= iRows; i++) {
listWheres.clear();
listSets.clear();
sbStmt.setLength(0);
sbWhere.setLength(0);
sbSet.setLength(0);
sbStmt.append("UPDATE " + table + " ");
currRow = xssfSheet.getRow(i);
for (int j = 0; j < iCol; j++) {
if (boolArray[j]) {
sbWhere.setLength(0);
sbWhere.append(xssfSheet.getRow(0).getCell(j).getStringCellValue() + " = ");
Cell cell = xssfSheet.getRow(i).getCell(j);
if (isCellNumeric(cell)) {
sbWhere.append("" + (int) cell.getNumericCellValue());
} else if (isCellString(cell)) {
String strCleaned = cleanString(cell.getStringCellValue());
sbWhere.append("'" + strCleaned + "'");
}
listWheres.add(new String(sbWhere.toString()));
} else {
sbSet.setLength(0);
sbSet.append(xssfSheet.getRow(0).getCell(j).getStringCellValue() + " = ");
Cell cell = xssfSheet.getRow(i).getCell(j);
if (isCellNumeric(cell)) {
sbSet.append("" + (int) cell.getNumericCellValue());
} else if (isCellString(cell)) {
String strCleaned = cleanString(cell.getStringCellValue());
sbSet.append("'" + strCleaned + "'");
}
listSets.add(new String(sbSet.toString()));
}
}
Iterator<String> iterator = listSets.iterator();
if (iterator.hasNext()) {
sbStmt.append("SET " + iterator.next());
while (iterator.hasNext()) {
sbStmt.append(", " + iterator.next());
}
sbStmt.append(" ");
}
iterator = listWheres.iterator();
if (iterator.hasNext()) {
sbStmt.append(" WHERE " + iterator.next());
while (iterator.hasNext()) {
sbStmt.append(" AND " + iterator.next());
}
}
sbTotal.append(sbStmt.toString() + ";\n");
System.out.println(sbStmt.toString());
}
return new String(sbTotal.toString());
}
