{"id":5415,"date":"2023-09-26T02:58:55","date_gmt":"2023-09-26T01:58:55","guid":{"rendered":"https:\/\/www.baeldung.com\/java-expand-columns-with-apache-poi"},"modified":"2023-09-26T02:58:55","modified_gmt":"2023-09-26T01:58:55","slug":"expand-columns-with-apache-poi","status":"publish","type":"post","link":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/2023\/09\/26\/expand-columns-with-apache-poi\/","title":{"rendered":"Expand Columns with Apache POI"},"content":{"rendered":"

\"\"<\/p>\n

1. Overview<\/h2>\n
<\/div>\n

Apache POI is a popular Java API to manipulate different kinds of Microsoft Office documents such as Word<\/a>, Excel<\/a>, and PowerPoint<\/a> programmatically.<\/p>\n

It’s quite often that we need to expand columns in Excel spreadsheets. This is a common requirement when we produce spreadsheets for people to read.<\/strong> This helps readers to visualize the content in the columns better, which can’t be done with the default column size.<\/p>\n

In this tutorial, we’ll learn how to use the API to adjust the column width manually and automatically in Excel spreadsheets.<\/p>\n

2. Dependencies<\/h2>\n
<\/div>\n

First of all, we’ll need the following Apache POI<\/a> dependencies in our Maven pom.xml<\/em>:<\/p>\n

<dependency> \r\n    <groupId>org.apache.poi<\/groupId>\r\n    <artifactId>poi<\/artifactId> \r\n    <version>5.2.3<\/version> \r\n<\/dependency> \r\n<dependency> \r\n    <groupId>org.apache.poi<\/groupId> \r\n    <artifactId>poi-ooxml<\/artifactId> \r\n    <version>5.2.3<\/version> \r\n<\/dependency><\/code><\/pre>\n
3. Spreadsheet Preparation<\/h2>\n
<\/div>\n
Let’s start by having a quick revision on creating an Excel spreadsheet. We’ll prepare an Excel spreadsheet and populate some data into it for demonstration purposes:<\/p>\n
Workbook workbook = new XSSFWorkbook();\r\nSheet sheet = workbook.createSheet("NewSheet");\r\nRow headerRow = sheet.createRow(0);\r\nCell headerCell1 = headerRow.createCell(0);\r\nheaderCell1.setCellValue("Full Name");\r\nCell headerCell2 = headerRow.createCell(1);\r\nheaderCell2.setCellValue("Abbreviation");\r\nRow dataRow = sheet.createRow(1); \r\nCell dataCell1 = dataRow.createCell(0);\r\ndataCell1.setCellValue("Java Virtual Machine"); \r\nCell dataCell2 = dataRow.createCell(1);\r\ndataCell2.setCellValue("JVM");\r\n\/\/ More data rows created here...<\/code><\/pre>\n
Now, if we open the generated spreadsheet in Excel, we’ll see that every column has the same default width:
\n
\n\"\"<\/a>
\n
\nObviously, the content within the 1st column is too long and truncated due to the limited column width.<\/p>\n
4. Width Adjustment<\/h2>\n
<\/div>\n
Apache POI offers two different ways to adjust the column width. We can choose either way depending on our own requirements. Let us explore both approaches now.<\/p>\n
4.1. Fixed Width Adjustment<\/h3>\n
<\/div>\n
We can expand a particular column to a fixed width by calling setColumnWidth()<\/em> on the target Sheet<\/em> instance.<\/strong> There are two arguments in this method, which are columnIndex<\/em> and width, <\/em>respectively.<\/p>\n
It’s complex to manually derive the column width showing all the content, as it depends on various factors such as font type and font size.<\/strong> According to the definition of setColumnWidth()<\/em> in the API doc<\/a>, the width<\/em> argument is in units of 1\/256th of a character width.<\/p>\n
Given the default font Calibri with font size 11 in Excel, we could use the number of characters in the cell * 256 for the column width as a rough approximation:<\/p>\n
String cellValue = row.getCell(0).getStringCellValue();\r\nsheet.setColumnWidth(0, cellValue.length() * 256);<\/code><\/pre>\n
After the adjustment, we’ll see the entire content in the 1st column:
\n
\n\"\"<\/a><\/p>\n
It’s kind of a hassle to derive the column width by ourselves. Especially when we are dealing with spreadsheets containing numerous data rows. We must go through each row to identify the maximum character count. The presence of columns that include different fonts and font sizes adds further complexity to the width calculation.<\/p>\n
4.2. Auto Width Adjustment<\/h3>\n
<\/div>\n
Fortunately, Apache POI provides a convenient method, autoSizeColumn(),<\/em> to adjust the column width automatically.<\/strong> This ensures the content of the column can be fully visible to readers.<\/p>\n
autoSizeColumn()<\/em> requires only the column<\/em> argument, which is a zero-based column index. We can use the following code to auto-adjust the column width on the 1st column:<\/p>\n
sheet.autoSizeColumn(0);<\/code><\/pre>\n
We’ll see the following if we apply autoSizeColumn()<\/em> to every column. All columns’ content is now fully visible to the readers without any truncation:
\n
\n\"\"<\/a><\/p>\n
5. Conclusion<\/h2>\n
<\/div>\n
In this article, we’ve explored two different approaches in Apache POI to adjusting the column width of Excel spreadsheets: Fixed width adjustment and auto width adjustment. Adjusting column widths is essential for improving readability and creating reader-friendly Excel spreadsheets.<\/p>\n
As usual, the source code for the article is available over on GitHub<\/a>.<\/p>\n
\"\"<\/p>\n
<\/a> <\/a> <\/a> <\/a> <\/a> <\/a> <\/a> <\/div>\n\n
Commercials Cooperation Advertisements:<\/b><\/h2>\r\n

(1) IT Teacher IT Freelance
 <\/p>\r\nIT\u96fb\u8166\u88dc\u7fd2<\/a>
\u7acb\u523b\u8a3b\u518a\u53ca\u5831\u540d\u96fb\u8166\u88dc\u7fd2\u8ab2\u7a0b\u5427! <\/a>
\r\n\r\n\u7535\u5b50\u8ba1\u7b97\u673a -\u6559\u80b2 -IT \u96fb\u8166\u73ed\u201d ( IT\u96fb\u8166\u88dc\u7fd2 ) \u63d0\u4f9b\u4e00\u500b\u65b9\u4fbf\u7684\u7535\u5b50\u8ba1\u7b97\u673a \u6559\u80b2\u5e73\u53f0, \u70ba\u5927\u5bb6\u914d\u5c0d\u4fe1\u606f\u6280\u672f, \u96fb\u8166 \u8001\u5e2b, IT freelance \u548c programming expert. \u8b93\u5927\u5bb6\u65b9\u4fbf\u5730\u5c31\u80fd\u627e\u5230\u5408\u9069\u7684\u96fb\u8166\u88dc\u7fd2, \u96fb\u8166\u73ed, \u5bb6\u6559, \u79c1\u4eba\u8001\u5e2b.  
\r\n\r\nWe are a education and information platform which you can find a IT private tutorial teacher or freelance. 
\r\n\r\nAlso we provide different information about information technology, Computer, programming, mobile, Android, apple, game, movie, anime, animation\u2026 \r\n<\/p>\n

(2) ITSec
 <\/p>https:\/\/itsec.vip\/\r\nSecure<\/a> \r\n

 \r\n
www.ITSec.vip<\/a> 
 
 \r\n
www.Sraa.com.hk<\/a> 
 
 \r\n
www.ITSec.hk<\/a> 
 
 \r\n
www.Penetrationtest.hk<\/a> 
 
 \r\n
www.ITSeceu.uk<\/a> 
 
 \r\nSecure Your Computers from Cyber Threats and mitigate risks with professional services to defend Hackers. 

\r\nITSec provide IT Security and Compliance Services, including IT Compliance Services, Risk Assessment, IT Audit, Security Assessment and Audit, ISO 27001 Consulting and Certification, GDPR Compliance Services, Privacy Impact Assessment (PIA), Penetration test, Ethical Hacking, Vulnerabilities scan, IT Consulting, Data Privacy Consulting, Data Protection Services, Information Security Consulting, Cyber Security Consulting, Network Security Audit, Security Awareness Training. \r\n

Contact us right away. 

Email (Prefer using email to contact us): 
SalesExecutive@ITSec.vip<\/p>","protected":false},"excerpt":{"rendered":"
\"\"<\/p>\n
Explore two approaches in Apache POI to adjusting the column width of Excel spreadsheets.<\/p>\n
<\/a>\u00a0<\/a>\u00a0<\/a>\u00a0<\/a>\u00a0<\/a>\u00a0<\/a>\u00a0<\/a>\u00a0<\/div>\n","protected":false},"author":257,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[22],"tags":[61,122,127,129,124,128,125,132,131,133,126,130,123,66,94,88,97,56,64,65,60,112,40,75,95,104,33,120,2474,105,101,98,115,30,29,41,86,70,69,68,72,71,26,118,108,87,46,55,48,52,54,51,50,83,62,58,57,109,35,59,63,85,79,82,96,80,27,81,114,44,42,43,45,38,39,110,117,100,111,116,73,89,90,92,91,93,84,78,37,102,34,36,77,67,74,99,113,119,28,121,32,47,49,53,103,31,76],"class_list":["post-5415","post","type-post","status-publish","format-standard","hentry","category-mobile","tag-airpods","tag-anime","tag-anime-characters","tag-anime-cosplay","tag-anime-edits","tag-anime-merchandise","tag-anime-movies","tag-anime-news","tag-anime-recommendations","tag-anime-reviews","tag-anime-series","tag-anime-streaming","tag-animes","tag-app-store","tag-app-store-samsung","tag-appgallery","tag-appgallery-oneplus","tag-apple","tag-apple-music","tag-apple-tv","tag-apple-watch","tag-bbc-sport","tag-best-mobile-games","tag-bixby","tag-bixby-xiaomi","tag-champions-league","tag-cyberpunk","tag-cyberpunk-2077","tag-excel","tag-fantasy-football","tag-fifa","tag-football","tag-formula-1","tag-fortnite","tag-free-fire","tag-free-mobile-games","tag-freebuds-pro","tag-galaxy-a52","tag-galaxy-note-20","tag-galaxy-s21","tag-galaxy-watch-4","tag-galaxy-z-fold-3","tag-game","tag-games","tag-golf","tag-harmonyos","tag-how-to-backup-iphone","tag-how-to-factory-reset-iphone","tag-how-to-reset-iphone","tag-how-to-restore-iphone","tag-how-to-unlock-iphone","tag-how-to-unlock-iphone-5","tag-how-to-unlock-iphone-6","tag-huawei","tag-ios","tag-ipad","tag-iphone","tag-live-soccer","tag-lol","tag-macbook","tag-macos","tag-mate-40-pro","tag-mi-11-lite","tag-mi-home-security-camera-basic-1080p","tag-mi-home-security-camera-basic-1080p-huawei","tag-mi-smart-band-6","tag-minecraft","tag-miui","tag-mlb-scores","tag-mobile-game-design","tag-mobile-game-development","tag-mobile-game-marketing","tag-mobile-game-monetization","tag-mobile-games","tag-mobile-gaming","tag-nba-scores","tag-nba-standings","tag-nfl","tag-nfl-scores","tag-nhl-scores","tag-one-ui","tag-oneplus","tag-oneplus-9-pro","tag-oneplus-buds-pro","tag-oneplus-nord-ce-5g","tag-oxygenos","tag-p40-pro-plus","tag-poco-x3-pro","tag-pokemon","tag-premier-league","tag-pubg","tag-pubg-mobile","tag-redmi-note-10-pro","tag-samsung","tag-samsung-pay","tag-soccer","tag-sports","tag-steam","tag-steeam","tag-top-10-anime","tag-valorant","tag-when-do-the-iphone-7-come-out","tag-when-does-the-iphone-7-come-out","tag-when-is-the-iphone-7-coming-out","tag-world-cup","tag-xbox-series-x","tag-xiaomi"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/posts\/5415","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/users\/257"}],"replies":[{"embeddable":true,"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/comments?post=5415"}],"version-history":[{"count":1,"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/posts\/5415\/revisions"}],"predecessor-version":[{"id":5416,"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/posts\/5415\/revisions\/5416"}],"wp:attachment":[{"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/media?parent=5415"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/categories?post=5415"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gamefootballmobileanimeiphone.com\/index.php\/wp-json\/wp\/v2\/tags?post=5415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}