Programatically Output a Excel sheet

Programatically Output a Excel sheet commands

1.Define output file name
String outputFilePath = pathComponent(secondInput)+"\\Output.xlsx";

2.Define outputstream using output file name
FileOutputStream outputStream = new FileOutputStream(outputFilePath);

3.Initiate Workbook
XSSFWorkbook workbook = new XSSFWorkbook();

4.Createsheet
XSSFSheet sheet = workbook.createSheet("Same_comps_different_versions");

5.Createrow
Row row = sheet.createRow(rowCount++);

6.Createcell
Cell cell = row.createCell(columnCount++); 

7.Set cell value
cell.setCellValue("");

8.Write workbook object to output stream file and close both
workbook.write(outputStream);
outputStream.close();
workbook.close();


9. Column width
sheet.setColumnWidth(0, 1500);
sheet.setColumnWidth(1, 30000);
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 4000);
sheet.setColumnWidth(5, 4000);
sheet.setColumnWidth(6, 4000);
sheet.setColumnWidth(7, 10000);

Other important functions
------------------------------
USE XSSFCellStyle instead of CellStyle for adding custom colors
XSSFCellStyle  stylecolor = wb.createCellStyle();
//stylecolor.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());new XSSFColor( Color.decode("#7CFC00"))
stylecolor.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 255, 217)));
stylecolor.setFillPattern(CellStyle.SOLID_FOREGROUND);



1.Initializing Cell Style
CellStyle stylehead = workbook.createCellStyle();
CellStyle stylebold = workbook.createCellStyle(); //cell.setCellStyle(stylebold);
CellStyle stylecolor = workbook.createCellStyle(); //cell.setCellStyle(stylecolor);

2.Defining different CellStyle
XSSFFont font = workbook.createFont();
font.setBold(true);
stylebold.setFont(font);
stylecolor.setWrapText(true);

2.1Defining color
stylecolor.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
stylecolor.setFillPattern(CellStyle.SOLID_FOREGROUND);

3.Setting cellstyle to cell 
cell.setCellStyle(stylecolor); //put below after setting the value
cell.setCellStyle(stylebold);

4. Autosize column
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);

5. Autofilter on entire table
sheet.setAutoFilter(new CellRangeAddress(Top,Bottom , left, right)); //new CellRangeAddress(Top,Bottom , left, right)

6. Setting Alignment
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);//both sets the font to middle


7. SettingBorder
	stylewrap.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	stylewrap.setBorderTop(HSSFCellStyle.BORDER_THIN);
	stylewrap.setBorderRight(HSSFCellStyle.BORDER_THIN);
	stylewrap.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	
8: Hyper link
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=2ahUKEwjiwLy7ssvmAhU1zTgGHe7bBsIQFjAAegQIBhAB&url=http%3A%2F%2Fsvn.apache.org%2Frepos%2Fasf%2Fpoi%2Ftrunk%2Fsrc%2Fexamples%2Fsrc%2Forg%2Fapache%2Fpoi%2Fxssf%2Fusermodel%2Fexamples%2FHyperlinkExample.java&usg=AOvVaw3_0Q1TqpSZ-gDdMg9bd0Hz
https://stackoverflow.com/a/42361447/2018343
9: combining  styles
https://stackoverflow.com/a/32787930/2018343

Filling Tactics
-----------------
for (String string : tempc) {
			//System.out.printf("%s 		%s%n",++Z, string.replaceAll("=",","));
			partcommon = string.split("=");
			rowcommon = sheetcommon.createRow(rowCount++); //<---------------------------------------------------------------create row

			for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
				cell = rowcommon.createCell(f); //<----------------------------------------------------------------create cell
				switch (f) {
				case 0://list

					cell.setCellValue(String.valueOf(++Z)); //<----------------set cell value for sno
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 1://list1
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partcommon[0]);//<----------------set cell value for name
					//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 2://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partcommon[1]);//<----------------set cell value for version
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 3://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partcommon[2]);//<----------------set cell value for usage
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;

				default:
					break;
				}
			}	
			//rowcommon = sheetcommon.createRow(rowCount++);
}

------------------

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

required 6 libs
commons-collections4-4.1.jar
jsoup-1.10.1.jar
poi-3.15.jar
poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar
xmlbeans-2.6.0.jar