Java uses poi to export data to excel
Background:
In the previous article, we used jtds to connect to the database to obtain the corresponding data. This article describes how to use poi to store data everywhere in excel. This program is an Application
Body: third-party poi jar package: poi driver package download code snippet:
/*** Export data to excel * @ param data to be imported into excel * @ throws IOException */public void crateTempFile (ArrayList
> Data) throws IOException {// create an excel Workbook HSSFWorkbook wb = new HSSFWorkbook (); // create a sheetHSSFSheet sheet = wb. createSheet ("test"); // create a cell style HSSFCellStyle commonTitileStyle = wb. createCellStyle (); // create a font style HSSFFont font = wb. createFont (); // set the font color. setColor (HSSFColor. RED. index); // HSSFColor. VIOLET. index // font color // set the font size. setFontHeightInPoints (short) 12); // font size. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD); // put the font style into the cell style commonTitileStyle. setFont (font); // create a row HSSFRow titleRow = sheet. createRow (0); Map
TitleMap = data. get (0); Set
KeySet = titleMap. keySet (); Iterator
It = keySet. iterator (); // index value of the row in sheet int indexOfRow = 0; // create the first cell HSSFCell titititilecell = titleRow in the first row. createCell (indexOfRow); String titilKey = "test (6072)"; // sets the cell width sheet. setColumnWidth (indexOfRow, 20*256); // assign titileCell to the cell. setCellValue (titilKey); // sets the cell style titileCell. setCellStyle (commonTitileStyle); indexOfRow = 1; // set the second row of sheet HSSFRow dataRow = sheet. createRow (indexOfRow); while (it. hasNext () {// traverse to create the cell format of the second row HSSFCell cell1 = dataRow. createCell (indexOfRow-1); String key1 = it. next (); // change the field name in the database table to the expected title name key1 = dbColumnNameToExcelColumnName (key1) in excel; sheet. setColumnWidth (indexOfRow, 20*256); cell1.setCellValue (key1); indexOfRow ++ ;}// set the third row indexOfRow = 2; for (Map
Temp: data) {HSSFRow titleRow1 = sheet. createRow (indexOfRow); Set
Set = temp. keySet (); Iterator
It1 = set. iterator (); int indexOfColumn = 0; while (it1.hasNext () {String key = it1.next (); if (key. equals ("anchor_name") | key. equals ("send_gift_user_name") | key. equals ("add_date") {String value = temp. get (key); if (key. equals ("add_date") {Date d = new Date (Long. valueOf (value); SimpleDateFormat sdf = new SimpleDateFormat ("MM dd, yyyy HH: mm: ss"); value = sdf. format (d);} HSSFCell cell = titleRow1.createCell (indexOfColumn); cell. setCellValue (value);} else {long value = Long. valueOf (temp. get (key); HSSFCell cell = titleRow1.createCell (indexOfColumn); cell. setCellValue (value);} indexOfColumn ++;} indexOfRow ++;} FileOutputStream fos = new FileOutputStream (new File ("D: // test //" + new Random (). nextInt (100) + ". xls "); wb. write (fos); fos. close ();}
Effect: Create an excel file in D: \ test. The style is: Experience: Use poi to export data to excel. The procedure is as follows: 1. Create an excel Workbook, create a sheet, and 2. Set the values of each column in each row in the created sheet. For details about the style, refer to the API documentation provided by poi.