Java Export to Excel - Format Column - Right Align

Hi Chaps,

I have an Javascript (Export to Excel) function.
I want to set certain column formats to align=“right”, but can’t figure a way to do it.
If someone has any idea, I’d most appreciate it!

<script language="javascript" type="text/javascript">
function ExportToExcel() {
	input_box=confirm("Export to Microsoft Excel?");
		if (input_box==true) {
			
			var xlApp = new ActiveXObject("Excel.Application");
			// Silent-mode:
			xlApp.Visible = true;
			xlApp.DisplayAlerts = false;
			var xlBook = xlApp.Workbooks.Add();
			xlBook.worksheets("Sheet1").activate;
			var XlSheet = xlBook.activeSheet;
			XlSheet.Name="Balthasar - Costing Sheet";
			
			// Store the sheet header names in an array
			var rows = tblreport_invoice.getElementsByTagName("tr");
			var columns = tblreport_invoice.getElementsByTagName("th");
			var data = tblreport_invoice.getElementsByTagName("td");
			
  			// Set Excel Column Headers and formatting from array
			for(i=0;i<columns.length;i++){
				XlSheet.cells(1).value= "Balthasar Costing / Discount Sheet";
	   			XlSheet.cells(3,i+1).value= columns[i].innerText; //XlSheetHeader[i];
	   			XlSheet.cells(3,i+1).font.color="6";
	   			XlSheet.cells(3,i+1).font.bold="true";
	   			XlSheet.cells(3,i+1).interior.colorindex="37";
				XlSheet.Range("I4:I200").NumberFormat = "€#,##0.00";
				XlSheet.Range("J4:J200").NumberFormat = "€#,##0.00";
				XlSheet.Range("M4:M200").NumberFormat = "€#,##0.00";
				XlSheet.Range("N4:N200").NumberFormat = "€#,##0.00";
				XlSheet.Range("O4:O200").NumberFormat = "€#,##0.00";
				XlSheet.Range("P4:P200").NumberFormat = "€#,##0.00";
				XlSheet.Range("Q4:Q200").NumberFormat = "€#,##0.00";
			}
			
			//run over the dynamic result table and pull out the values and insert into corresponding Excel cells
			var d = 0;
			for (r=4;r<rows.length+3;r++) { // start at row 2 as we've added in headers - so also add in another row!
				for (c=1;c<columns.length+1;c++) {
					XlSheet.cells(r,c).value = data[d].innerText;
					d = d + 1;
				}
			}
			
			//autofit the columns
			XlSheet.columns.autofit;
			
			// Make visible:
			xlApp.visible = true;
			xlApp.DisplayAlerts = true;
			CollectGarbage();
			//xlApp.Quit();
		}
}
</script>

See: http://www.greggriffiths.org/webdev/both/excel/activex.html

Cool, thanks dude, checked out the link and found:

XlSheet.Range(“A1:B1000”).HorizontalAlignment = -4131;

Which results in a LEFT Align cell, but can’t find what the numerical value for RIGHT is?

Got it: -4152…Cheers dude

The paragraph below that code block shows

The reason we use the actual values - such as -4131 and not the Excel Constant of xlLeft - is that as we are interacting via ActiveX we don’t have visibility of these constants, a full list of the real value of the constants is also available.

Not that useful because the link is now dead, but it does give something to go on.

Sometimes it’s not easy hunting down information, so here’s a blow-by-blow account of how to go about it.

www.archive.org does show several copies of that now-dead page http://web.archive.org/web/*/http://www.geocities.com/ResearchTriangle/9834/msexcel8.htm

The most recent version shows

#define xlBottom  0xffffeff5
#define xlLeft  0xffffefdd
#define xlRight  0xffffefc8
#define xlTop  0xffffefc0

But how do you get from 0xffffefdd to -1431 ? Who knows. Let’s look for info from a different site, for we need confirmation info anyway.

Google search for

php xlleft xlright -4131

and you won’t find anything useful. The negative sign tells google to ignore anything with that term, so let’s search for

php xlleft xlright 4131

instead.

Result 1 at http://www.swissdelphicenter.ch/torry/showcode.php?id=156 has:

// Format Cells
xlBottom = -4107;
xlLeft = -4131;
xlRight = -4152;
xlTop = -4160;

Result 2 at http://www.daniweb.com/forums/thread200456.html has:

# Alignment Variables
xlLeft, xlRight, xlCenter = -4131, -4152, -4108

That’s confirmation enough. It’s -4152 to align to the right.