search envelope-o feed check
Home Unanswered Active Tags New Question
user comment-o

How to export excel with table

Asked by MoonStar
4 years ago.

Hello Dan,

Can i ask some question ?? I have a problem to export excel with table.
In the following code ,it can be export excel but not with table format.It's need to change table format in excel because we need to change border style,font color,background color etc..

In javascript, i write the following.
function export(){
var sql;
var db = new TenantDatabase();
sql="select * from TABLE";
var DATASIZE = db.execute(sql);

var csvData = [];
var title = "Meeting Room Booking Information";

var columnValue=[];//q2
columnValue.push("Room Name");
columnValue.push("Project Name");
columnValue.push("Date");
columnValue.push("Start Time");
columnValue.push("End Time");

csvData.push(escapeRow(columnValue));

for(var start=0 ;start <= DATASIZE.data.length ;start += FETCHSIZE) {
var result=db.fetch(sql,start,FETCHSIZE ,[])
if (result.error) {
Transfer.toErrorPage({
title: 'Error',
message: 'Failed to get data from table.',
detail: result.errorMessage
});
}
for (var i = 0; i < result.data.length; i++) {
var roomId = result.data[i].room_id;
var projectName=result.data[i].project_Name;
var startDate = result.data[i].start_date;
var startHours = result.data[i].startime;
var endHours = result.data[i].endtime;

csvData.push(escapeRow(result.data[i]));
}
}
Module.download.send(csvData.join('\n'), 'MeetingRoomBookingExport.xls', 'text/xls');
}

function escapeRow(data) {
var rowData = [];
for (var i in data) {
rowData.push(data[i]);
}
return rowData.join('\t');
}

function escapeColumn(value) {
return '"' + value.replace(/\r\n/g, '\n').replace(/\r/g, '\n').replace(/\"/g, '\"\"') + '"';
}
}

I want to show the data from sql with following format in excel.
Please reply if u have a time.

Answer posted by Dan Letecky [DayPilot]
4 years ago.

You are exporting the data in CSV (https://en.wikipedia.org/wiki/Comma-separated_values) format which doesn't support any formatting.

Basically, there are two options:

1. You'll need to use a third-party library that will let you create a true xls/xlsx file.

2. Another option is to export the data in Excel XML Spreadsheet format (XMLSS) which is not as complicated and can be created manually - see "Excel XML Spreadsheet example" at https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats

However, you will see a warning when trying to open such file in Excel.

Comment posted by MoonStar
4 years ago.

Dear Dan,

Thanks for your reply.

This question is more than 3 months old and has been closed. Please create a new question if you have anything to add.