• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Need to find the width of a column in excel using cfspreadsheet.

Explorer ,
Jan 02, 2023 Jan 02, 2023

Copy link to clipboard

Copied

Saw note to use getColumn width from a static function but don't know how to call it within coldfusion.

 

static int getWidth(Sheet sheet, int col) {  int width = sheet.getColumnWidth(col);  if (width == sheet.getDefaultColumnWidth()) {    width = (int) (width * 256);  }  return width;}

 

Views

159

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 2 Correct answers

Community Beginner , Jan 06, 2023 Jan 06, 2023

Robert,

It is relatively easy to use the underlying POI library once you get a hook into it with CF.

 

 

<!--- create the spreadsheet with ColdFusion --->
<cfset sObj = SpreadsheetNew("mySpreadsheet") />

<!--- fill your spreadsheet with your data / query however --->


<!--- get a handle on the ColdFusion underlying POI object --->
<cfset objWorkbook = sObj.getWorkbook() />

<!--- get a handle on the workbook's sheet --->
<cfset objSheet = objWorkbook.getSheetAt(objWorkbook.getActiveSheetIndex()) />

<!--- the

...

Votes

Translate

Translate
Community Expert , Jan 09, 2023 Jan 09, 2023

Some additional suggestions to @Kevin D. Wright 's code:

  • For completeness, place the following line at the top
    <cfparam name="variables.columnIndex" default="0">​
    This is related to the next bullet-point.
  • Be aware that the code is Java, hence column numbering starts with 0.
  • The unit of measurement of the width is (1/256)th of the width of a single character. So a width of 2048 would be equivalent to a space occupied by 8 characters.

 

See for example https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Sheet.html#getColumnWidth-int-

...

Votes

Translate

Translate
Community Beginner ,
Jan 06, 2023 Jan 06, 2023

Copy link to clipboard

Copied

Robert,

It is relatively easy to use the underlying POI library once you get a hook into it with CF.

 

 

<!--- create the spreadsheet with ColdFusion --->
<cfset sObj = SpreadsheetNew("mySpreadsheet") />

<!--- fill your spreadsheet with your data / query however --->


<!--- get a handle on the ColdFusion underlying POI object --->
<cfset objWorkbook = sObj.getWorkbook() />

<!--- get a handle on the workbook's sheet --->
<cfset objSheet = objWorkbook.getSheetAt(objWorkbook.getActiveSheetIndex()) />

<!--- then you can reference the columns within the sheet and get it's width --->
<cfset myColWidth = objSheet.getColumnWidth(columnIndex) />

 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

Some additional suggestions to @Kevin D. Wright 's code:

  • For completeness, place the following line at the top
    <cfparam name="variables.columnIndex" default="0">​
    This is related to the next bullet-point.
  • Be aware that the code is Java, hence column numbering starts with 0.
  • The unit of measurement of the width is (1/256)th of the width of a single character. So a width of 2048 would be equivalent to a space occupied by 8 characters.

 

See for example https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Sheet.html#getColumnWidth-int- 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

LATEST

Thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation