This tutorial will provide you with detailed instructions on how to query book data from a database using Java and generate a formatted Word statistics report. We will use the Spring Boot framework and the Apache POI library to implement this functionality.
1. Project structure
src/main/java
├── com/example/libraryreport
│ ├── controller
│ │ └──
│ ├── service
│ │ └──
│ ├── mapper
│ │ └──
│ └──
2. Core code implementation
1. Controller layer
@RestController @RequestMapping("/api/reports") public class ReportController { @Autowired private BookReportService bookReportService; @GetMapping("/books") public ResponseEntity<String> generateBookReport() { try { String fileName = "Book Statistics Report_" + () + ".docx"; String outputPath = "reports/" + fileName; (outputPath); return ("Report generation successfully: " + outputPath); } catch (Exception e) { return (500) .body("Report generation failed: " + ()); } } }
2. Data access layer interface
public interface BookReportMapper { // Get the total number of books int selectTotalBookCount(); // Statistics the number of books by category List<Map<String, Object>> selectBooksByCategory(); // Get the top 10 popular books List<Map<String, Object>> selectPopularBooks(); // Get borrowing statistics List<Map<String, Object>> selectBorrowStats(); }
3. Service layer implementation
@Service public class BookReportService { @Autowired private BookReportMapper bookReportMapper; public void generateReport(String outputPath) throws Exception { // Create Word Document XWPFDocument document = new XWPFDocument(); try { // Add report title addTitle(document, "Library Book Statistics Report"); // Add a generation date addGenerationDate(document); // Add total book count addTotalBookCount(document); // Add a classification statistics table addCategoryStatistics(document); // Add a list of popular books addPopularBooks(document); // Add borrowing statistics addBorrowStatistics(document); // Save the document saveDocument(document, outputPath); } finally { (); } } // Create a report main title, set centered, bold and large fonts private void addTitle(XWPFDocument document, String titleText) { XWPFParagraph title = (); (); XWPFRun titleRun = (); (titleText); (true); (20); ("Songyi"); // Add empty lines (); } // Add report generation date in the upper right corner private void addGenerationDate(XWPFDocument document) { XWPFParagraph datePara = (); (); XWPFRun dateRun = (); ("Generation Date: " + ().toString()); (12); ("Songyi"); // Add empty lines (); } // Show the total library collection private void addTotalBookCount(XWPFDocument document) { int totalCount = (); XWPFParagraph sectionTitle = createSectionTitle(document, "1. Statistics of total books"); XWPFParagraph content = (); XWPFRun run = (); (("The library currently has a total collection of books: %s volumes", formatNumber(totalCount))); (14); ("Songyi"); // Add empty lines (); } // Create a classification statistics table, including serial number, classification name, quantity and proportion private void addCategoryStatistics(XWPFDocument document) throws Exception { // Create chapter title XWPFParagraph sectionTitle = createSectionTitle(document, "2. Book classification statistics"); // Get classified data List<Map<String, Object>> categories = (); int total = (); // Create a table XWPFTable table = (1, 4); ("100%"); // Set the table header setTableHeader(table, "Serial Number", "Book Classification", "quantity", "Percentage"); // Fill in data int index = 1; for (Map<String, Object> category : categories) { XWPFTableRow row = (); (0).setText((index++)); (1).setText(("categoryName").toString()); int count = (("count").toString()); (2).setText(formatNumber(count)); double percent = (count * 100.0) / total; (3).setText(("%.1f%%", percent)); } // Add summary line XWPFTableRow footer = (); (1).setText("total"); (2).setText(formatNumber(total)); (3).setText("100%"); // Add empty lines (); } // List the top 10 popular books, the top three are bolded in blue private void addPopularBooks(XWPFDocument document) { // Create chapter title XWPFParagraph sectionTitle = createSectionTitle(document, "Three, Top 10 Popular Books"); List<Map<String, Object>> popularBooks = (); for (int i = 0; i < (); i++) { Map<String, Object> book = (i); XWPFParagraph item = (); (200); XWPFRun run = (); (("%d. 《%s》 - %s (Borrowing volume: %sSecond-rate)", i + 1, ("title"), ("author"), formatNumber((("borrowCount").toString())) ); (12); ("Songyi"); // Highlight the top three if (i < 3) { (true); ("0000FF"); } } // Add empty lines (); } // Create a borrowing statistics table to display monthly borrowing volume and year-on-year growth rate private void addBorrowStatistics(XWPFDocument document) { // Create chapter title XWPFParagraph sectionTitle = createSectionTitle(document, "IV. Borrowing Statistics"); List<Map<String, Object>> borrowStats = (); // Create a table XWPFTable table = (1, 3); ("100%"); // Set the table header setTableHeader(table, "month", "Loan volume", "Year-over-year growth"); // Fill in data for (Map<String, Object> stat : borrowStats) { XWPFTableRow row = (); (0).setText(("month").toString()); (1).setText(formatNumber((("count").toString()))); // Calculate year-on-year growth if (("growthRate")) { double growth = (("growthRate").toString()); (2).setText(("%.1f%%", growth * 100)); // Set color: Grow to red, drop to green XWPFRun growthRun = (2).getParagraphs().get(0).getRuns().get(0); if (growth > 0) { ("FF0000"); } else if (growth < 0) { ("00FF00"); } } else { (2).setText("N/A"); } } } // Auxiliary method to create a unified chapter title format private XWPFParagraph createSectionTitle(XWPFDocument document, String title) { XWPFParagraph paragraph = (); XWPFRun run = (); (title); (true); (16); ("Songyi"); return paragraph; } // Auxiliary method, set the table header style private void setTableHeader(XWPFTable table, String... headers) { XWPFTableRow headerRow = (0); for (int i = 0; i < ; i++) { XWPFRun run = (i).getParagraphs().get(0).createRun(); (headers[i]); (true); (12); ("Songyi"); // Set cell background color (i).setColor("D3D3D3"); } } // Format the number display and add thousands separator private String formatNumber(long number) { return ("%,d", number); } // Save Word document to the specified path private void saveDocument(XWPFDocument document, String outputPath) throws IOException { // Make sure the directory exists File outputFile = new File(outputPath); ().mkdirs(); try (FileOutputStream out = new FileOutputStream(outputFile)) { (out); } } }
4. Operation effect
Document style details
-
Font specifications:
- "Zongyi" is used by default in Chinese, and the numbers/English are automatically matched
- The title level is clear (No. 20→No. 16→No. 14→No. 12)
-
Digital processing:
- All quantities are automatically added with thousand separators (e.g.
1,200
) - Percentage retains 1 decimal place (e.g.
28.4%
)
- All quantities are automatically added with thousand separators (e.g.
-
Color markers:
- Blue: Top 3 Popular Books
- Red/Green: Borrowing/Decreasing
- Gray: Table title background
-
Layout design:
- There are appropriate empty walks between chapters
- Table width accounts for full page (100%)
- List items are uniformly indented
-
Data Integrity:
- Including total statistics, classification proportion, rankings and trend analysis
- Automatically calculate percentage and growth rate
The specific results are as follows
+------------------------------------------+
| Library Book Statistics Report |
| |
| |
| Generation date: 2023-05-20 (small words in the upper right corner) |
| |
| 1. Statistics of total books |
|The library currently has a total of books: 12,345 volumes |
| |
| 2. Book classification statistics |
| +----+------------+-------+-------+ |
| |Serial number | Book classification | Quantity | Percentage | |
| +----+------------+-------+-------+ |
| |1 |Computer Science | 3,500 | 28.4% | |
| |2 |Literature | 2,800 | 22.7% | |
| ... |
| | |Total |12,345 | 100% | |
| +----+------------+-------+-------+ |
| |
| 3. Top 10 popular books |
| 1. "Java Programming Thoughts"... (Blue Bold) |
| 2. "Three-Body"... (bright blue) |
| ... |
| |
| 4. Borrowing Statistics |
| +------------+--------+----------+ |
| | Month | Borrowing | Year-on-year | |
| +------------+--------+----------+ |
| | 2023-01 | 1,200 | +15.2%↑ | |
| | 2023-02 | 980 | -8.3%↓ | |
| ... |
+-----------------------------------------+
5. Summary
Through this tutorial, we implemented:
- Building a web service using Spring Boot
- Accessing database through MyBatis to obtain statistics
- Generate formatted Word documents using Apache POI
- Implement output in complex formats such as tables and lists
This solution can be easily extended to other types of statistical report generation tools, just modify the SQL query and Word format settings.
This is the article about Java generation and formatting Word statistics report. For more related Java generation Word content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!