Báo cáo được tạo dễ dàng với Excel, XML, và các công nghệ Java, Phần 2

// File and exception handling imports
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;

// Apache POI imports
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;

// XOM imports
import nu.xom.Document;
import nu.xom.Elements;
import nu.xom.Element;
import nu.xom.Attribute;
import nu.xom.Serializer;

// Imports for later calculations
import java.text.NumberFormat;

public class ExcelXML {

   public static void main(String[] args) {

      try {

      // Put file and number handling code here.

   // End try
   }
   catch (IOException e) {
      System.out.println("File Input/Output Exception!");
   }
   catch (ParseException e) {
      System.out.println("Number Parse Exception!");
   }

   // End main method
   }

// End class block
}

<dawn>07:00</dawn>

// First, create a new XML Document object to load the Excel sheet into XML.
// To create an XML Document, first create an element to be its root.
Element reportRoot = new Element("sheet");

// Create a new XML Document object using the root element
Document XMLReport = new Document(reportRoot);

// Set up a FileInputStream to represent the Excel spreadsheet
FileInputStream excelFIS = new FileInputStream("C:\\Planet Power\\Employee_List.xls");

// Create an Excel Workbook object using the FileInputStream created above 
HSSFWorkbook excelWB = new HSSFWorkbook(excelFIS);

<font size="12">Hello, Planet!</font>

<sheet>
   <row>
      <cell>
         Thumb
      </cell>
      <cell>
         Green
      </cell>
      <cell>
         Growing Plants
      </cell>
      <cell>
         5:00 AM
      </cell>
      <cell>
         2:00 PM
      </cell>
      <cell>
         150,000
      </cell>
   </row>
</sheet>

<EmployeeData>
   <Employee>
      <EmployeeLastName>
         Thumb
      </EmployeeLastName>
      <EmployeeFirstName>
         Green
      </EmployeeFirstName>
      <MainSuperPower>
         Growing Plants
      </MainSuperPower>
      <DailyStartTime>
         5:00 AM
      </DailyStartTime>
      <DailyEndTime>
         2:00 PM
      </DailyEndTime>
      <Salary>
         150,000
      </Salary>
   </Employee>
</EmployeeData>

<sheet>
   <row>
      <EmployeeLastName>
         Thumb
      </EmployeeLastName>
      <EmployeeFirstName>
         Green
      </EmployeeFirstName>
      <MainSuperPower>
         Growing Plants
      </MainSuperPower>
      <DailyStartTime>
         5:00 AM
      </DailyStartTime>
      <DailyEndTime>
         2:00 PM
      </DailyEndTime>
      <Salary>
         150,000
      </Salary>
   </row>
</sheet>

// Traverse the workbook's rows and cells.   
// Remember, excelWB is the workbook object obtained earlier.

// Just use the first sheet in the book to keep the example simple.
// Pretend this is an outer loop (looping through sheets).

HSSFSheet oneSheet = excelWB.getSheetAt(0);

      // Now get the number of rows in the sheet
      int rows = oneSheet.getPhysicalNumberOfRows();    

      // Middle loop: Loop through rows in the sheet

         for (int rowNumber = 0; rowNumber < rows; rowNumber++) {
            HSSFRow oneRow = oneSheet.getRow(rowNumber);

            // Skip empty (null) rows.
            if (oneRow == null) {
               continue;
            }

            // Create an XML element to represent the row.
            Element rowElement = new Element("row");

 
            // Get the number of cells in the row
            int cells = oneRow.getPhysicalNumberOfCells();

            // Inner loop: Loop through each cell in the row

            for (int cellNumber = 0; cellNumber < cells; cellNumber++) {
               HSSFCell oneCell = oneRow.getCell(cellNumber);

               // If the cell is blank, the cell object is null, so don't 
               // try to use it. It will cause errors.
               // Use continue to skip it and just keep going.
               if (oneCell == null) {
                  continue;
               }

// Set up a string to use just "header" as the element name
// to store the column header cells themselves.

String elementName="header";

// Figure out the column position of the Excel cell.
int cellColumnNumber = oneCell.getColumnIndex();

// If on the first Excel row, don't change the element name from "header,"  
// because the first row is headers. Before changing the element name,
// test to make sure you're past the first row, which is the zero row.

if (rowNumber > 0)

 // Set the elementName variable equal to the column name
 elementName=reportRoot.getFirstChildElement("row").getChild(cellColumnNumber).getValue();

// Remove weird characters and spaces from elementName,
// as they're not allowed in element names.
elementName = elementName.replaceAll("[\\P{ASCII}]","");
elementName = elementName.replaceAll(" ", "");

// Create an XML element to represent the cell, using 
// the calculated elementName

Element cellElement = new Element(elementName);

// Create an attribute to hold the cell's format.
// May be repeated for any other formatting item of interest.
String attributeValue = oneCell.getCellStyle().getDataFormatString();
Attribute dataFormatAttribute = new Attribute("dataFormat", attributeValue);

// Add the attribute to the cell element
cellElement.addAttribute(dataFormatAttribute);

switch (oneCell.getCellType()) {

   case HSSFCell.CELL_TYPE_STRING:

      // If the cell value is string, create an attribute
      // for the cellElement to state the data type is a string

      Attribute strTypeAttribute = new Attribute("dataType", "String");
      cellElement.addAttribute(strTypeAttribute);

      // Append the cell text into the element
      cellElement.appendChild(oneCell.getStringCellValue());

      // Append the cell element into the row
      rowElement.appendChild(cellElement);

      break;

   case HSSFCell.CELL_TYPE_NUMERIC:
      // If the cell value is a number, create an attribute
      // for the cellElement to state the data type is numeric
      Attribute cellAttribute = new Attribute("dataType", "Numeric");

      // Add the attribute to the cell
      cellElement.addAttribute(cellAttribute);

      // Apply the formatting from the cells to the raw data
      // to get the right format in the XML. First, create an
      // HSSFDataFormatter object.

      HSSFDataFormatter dataFormatter = new HSSFDataFormatter();

      // Then use the HSSFDataFormatter to return a formatted string
      // from the cell rather than a raw numeric value:
      String cellFormatted = dataFormatter.formatCellValue(oneCell);

      //Append the formatted data into the element
      cellElement.appendChild(cellFormatted);

      // Append the cell element into the row
      rowElement.appendChild(cellElement);

      break;

      // End inner loop
      }

   // Append the row element into the root 
   // if the row isn't empty.  
   if (rowElement.getChildCount() > 0) {
      reportRoot.appendChild(rowElement);
   }

   // End middle loop 
   }

// To get employees' salaries, iterate through row elements and get a collection of rows

Elements rowElements = reportRoot.getChildElements("row");

// For each row element

for (int i = 0; i < rowElements.size(); i++) {

   // Get the salary element, 
   // Calculate 1% of it and store it in a donation element.
      // Unless it's the first row (0), which needs a header element.
   if (i==0) {
      Element donationElement = new Element("header");
      donationElement.appendChild("Donation");

      Attribute dataType = new Attribute("dataType","String");
      donationElement.addAttribute(dataType);

      Attribute dataFormat = new Attribute("dataFormat","General");
      donationElement.addAttribute(dataFormat);

      // Append the donation element to the row element.
      rowElements.get(i).appendChild(donationElement);
   }

   // If the row is not the first row, put the donation in the element.
   else {
      Element donationElement = new Element("Donation");

      Attribute dataType = new Attribute("dataType","Numeric");
      donationElement.addAttribute(dataType);

      // The dataFormat of the donation should be the same 
      // number format as salary, which looking at the XML file tells
      // us is "#,##0".
      Attribute dataFormat = new Attribute("dataFormat","#,##0");
      donationElement.addAttribute(dataFormat);

      // Get the salary element and its value
      Element salaryElement = rowElements.get(i).getFirstChildElement("Salary");
      String salaryString = salaryElement.getValue();

      // Calculate 1% of the salary. Salary is a string
      // with commas, so it 
      // must be converted for the calculation.

      // Get a java.text.NumberFormat object for converting string to a double
      NumberFormat numberFormat = NumberFormat.getInstance(); 

      // Use numberFormat.parse() to convert string to double.
      // Throws ParseException
      Number salaryNumber = numberFormat.parse(salaryString);

      // Use Number.doubleValue() method on salaryNumber to 
      // return a double to use in the calculation.
      // Perform the calculation to figure out 1%.
      double donationAmount = salaryNumber.doubleValue()*.01;

      // Append the value of the donation into the donationElement.
      // donationAmount is a double and must be converted to a string.
      donationElement.appendChild(Double.toString(donationAmount));

      // Append the donation element to the row element
      rowElements.get(i).appendChild(donationElement);

      //End else
      }

// End for loop 
}

   // Print out the XML version of the spreadsheet to see it in the console
   System.out.println(XMLReport.toXML());

   // To save the XML into a file for GEE WHIS, start with a FileOutputStream
   // to represent the file to write, C:\Planet Power\GEE_WHIS.xml.
   FileOutputStream hamsterFile = new FileOutputStream("C:\\Planet Power\\GEE_WHIS.xml");

   // Create a serializer to handle writing the XML
   Serializer saveTheHamsters = new Serializer(hamsterFile);

   // Set child element indent level to 5 spaces to make it pretty
   saveTheHamsters.setIndent(5);

   // Write the XML to the file C:\Planet Power\GEE_WHIS.xml
   saveTheHamsters.write(XMLReport);

// Create a new Excel workbook and iterate through the XML 
// to fill the cells.
// Create an Excel workbook object 
HSSFWorkbook donationWorkbook = new HSSFWorkbook();

// Next, create a sheet for the workbook. 
HSSFSheet donationSheet = donationWorkbook.createSheet(); 

// Iterate through the row elements and then cell elements

// Outer loop: There was already an elements collection of all row elements
// created earlier. It's called rowElements. 
// For each row element in rowElements:

for (int j = 0; j < rowElements.size(); j++) {

   // Create a row in the workbook for each row element (j)
   HSSFRow createdRow = donationSheet.createRow(j);

   // Get the cell elements from that row element and add them to the workbook.
   Elements cellElements = rowElements.get(j).getChildElements();

   // Middle loop: Loop through the cell elements.
   for (int k = 0; k < cellElements.size(); k++) { 

      // Create cells and cell styles. Use the row's
      // createCell (int column) method.
      // The column index is the same as the cell element index, which is k.
      HSSFCell createdCell = createdRow.createCell(k); 

      // To set the cell data format, retrieve it from the attribute 
      // where it was stored: the dataFormat attribute. Store it in a string.
      String dataFormatString = cellElements.get(k).getAttributeValue("dataFormat");

      // Create an HSSFCellStyle using the createCellStyle() method of the workbook.
      HSSFCellStyle currentCellStyle = donationWorkbook.createCellStyle();

      // Create an HSSFDataFormat object from the workbook's method
      HSSFDataFormat currentDataFormat = donationWorkbook.createDataFormat();

      // Get the index of the HSSFDataFormat to use. The index of the numeric format
      // matching the dataFormatString is returned by getFormat(dataFormatString).
      short dataFormatIndex = currentDataFormat.getFormat(dataFormatString);

      // Next, use the retrieved index to set the HSSFCellStyle object's DataFormat.
      currentCellStyle.setDataFormat(dataFormatIndex);

      // Then apply the HSSFCellStyle to the created cell.
      createdCell.setCellStyle(currentCellStyle);

      // Set cell value and types depending on the dataType attribute

      if (cellElements.get(k).getAttributeValue("dataType")=="String") {
         createdCell.setCellType(HSSFCell.CELL_TYPE_STRING);
         createdCell.setCellValue(cellElements.get(k).getValue());
      }

      if (cellElements.get(k).getAttributeValue("dataType")=="Numeric") {
         createdCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

         // In this spreadsheet, number styles are times, dates,
         // or salaries. To store as a number and not as text,
         // salaries should be converted to doubles first.
         // Dates and times should not be converted to doubles first,
         // or you'll be inputting the wrong date or time value.
         // Dates and times can be entered as Java Date objects.

         if (cellElements.get(k).getAttributeValue("dataFormat").contains("#")) {

            // If formatting contains a pound sign, it's not a date.
            // Use a Java NumberFormat to format the numeric type cell as a double,
            // because like before, the element has commas in it.
            NumberFormat numberFormat = NumberFormat.getInstance(); 
            Number cellValueNumber = numberFormat.parse(cellElements.get(k).getValue());
            createdCell.setCellValue(cellValueNumber.doubleValue());

            // Add a hyperlink to the fictional GEE WHIS Web site just
            // to demonstrate that you can.
            HSSFHyperlink hyperlink = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
            hyperlink.setAddress("http://www.ibm.com/developerworks/");
            createdCell.setHyperlink(hyperlink);
         }

         else {
            // if it's a date, don't convert to double
            createdCell.setCellValue(cellElements.get(k).getValue());
         }

      }

//  Handle formula and error type cells. See ExcelXML.java for the full example.

      //End middle (cell) for loop
      }
   // End outer (row) for loop 
   }

// Demonstrate functions:
// Add the TODAY() and NOW() functions at bottom of the Excel report
// to say when the workbook was opened.

// Find the last row and increment by two to skip a row
int lastRowIndex = donationSheet.getLastRowNum()+2;

// Create a row and three cells to hold the information.
HSSFRow lastRow = donationSheet.createRow(lastRowIndex);
HSSFCell notationCell = lastRow.createCell(0);
HSSFCell reportDateCell = lastRow.createCell(1);
HSSFCell reportTimeCell = lastRow.createCell(2);

// Set a regular string value in one cell
notationCell.setCellValue("Time:");

// Setting formula values uses setCellFormula()
reportDateCell.setCellFormula("TODAY()");
reportTimeCell.setCellFormula("NOW()"); 

// Create HSSFCellStyle objects for the date and time cells.
// Use the createCellStyle() method of the workbook.

HSSFCellStyle dateCellStyle = donationWorkbook.createCellStyle();
HSSFCellStyle timeCellStyle = donationWorkbook.createCellStyle();

// Get a HSSFDataFormat object to set the time and date formats for the cell styles
HSSFDataFormat dataFormat = donationWorkbook.createDataFormat();

// Set the cell styles to the right format by using the index numbers of
// the desired formats retrieved from the getFormat() function of the HSSFDataFormat.
dateCellStyle.setDataFormat(dataFormat.getFormat("m/dd/yy"));
timeCellStyle.setDataFormat(dataFormat.getFormat("h:mm AM/PM"));

// Set the date and time cells to the appropriate HSSFCellStyles.
reportDateCell.setCellStyle(dateCellStyle);
reportTimeCell.setCellStyle(timeCellStyle);

 
// Write out the workbook to a file. First,
// you need some sort of OutputStream to represent the file.
 
 String filePathString = "C:\\Planet Power\\Employee_Donations.xls";
 FileOutputStream donationStream = new FileOutputStream(filePathString);
 
 donationWorkbook.write(donationStream);