Vikas,
I have a servlet that uses JXL to build an Excel spreadsheet and send it via
email. Below is the code. However, I do not export any of the data from
Google Spreadsheets. If the Google Spreadsheets API doesn't let you get the
byte[], you'd probably need a JXL <-> Google Spreadsheets adapter that cycles
through all sheets and all cells and duplicates them to the other format.
Hope this helps,
MG
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Properties;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.mail.Address;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.Part;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.util.ByteArrayDataSource;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.google.appengine.api.utils.SystemProperty;
<.. snip...>
public class SendMailTask extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doPost(HttpServletRequest request, HttpServletResponse
response)
throws ServletException, IOException {
doGet(request, response);
}
public void doGet(HttpServletRequest request, HttpServletResponse
response)
throws ServletException, IOException {
String requestId = request.getParameter("requestId");
EmailTrackingRequest req =
dao.getRequest(Long.valueOf(requestId));
ByteArrayOutputStream outputStream = new
ByteArrayOutputStream();
WritableWorkbook workbookOut =
Workbook.createWorkbook(outputStream);
WritableSheet sheetOut = workbookOut.createSheet("First Sheet",
0);
Label salesOrderIdLabel = new Label (0,0,"Sales Order ID");
Label shipDateLabel = new Label (1,0,"Ship Date");
Label trackingNumLabel = new Label (2,0, "Tracking Number");
Label scanDateLabel = new Label(3,0, "Scan Date");
Label scanTimeLabel = new Label(4,0, "Scan Time");
Label scanOffsetLabel = new Label(5,0, "GMT Offset");
Label scanStatusLabel = new Label(6,0, "Scan Status");
Label scanLocLabel = new Label(7,0, "Location");
Label scanCommentsLabel = new Label(8,0, "Comments");
try {
sheetOut.addCell(salesOrderIdLabel);
sheetOut.addCell(shipDateLabel);
sheetOut.addCell(trackingNumLabel);
sheetOut.addCell(scanDateLabel);
sheetOut.addCell(scanTimeLabel);
sheetOut.addCell(scanOffsetLabel);
sheetOut.addCell(scanStatusLabel);
sheetOut.addCell(scanLocLabel);
sheetOut.addCell(scanCommentsLabel);
int currentRow = 1;
for (Shipment s : req.getShipments()) {
sheetOut.addCell(new Label (0, currentRow,
s.getSalesOrderId()));
sheetOut.addCell(new Label (1, currentRow,
s.getShipDate().toString()));
sheetOut.addCell(new Label (2, currentRow,
s.getTrackingNumber()));
for (Scan scan : s.getScans()) {
//_log.warning ("scan status: " +
scan.getStatus());
Label scanDate = new Label(3,
currentRow, scan.getScanDate());
Label scanTime = new Label(4,
currentRow, scan.getScanTime());
Label scanOffset = new Label(5,
currentRow, scan.getOffset());
Label scanStatus = new Label(6,
currentRow, scan.getScanStatus());
Label scanLoc = new Label(7,
currentRow, scan.getLoc());
Label scanComments = new Label(8,
currentRow, scan.getComments());
sheetOut.addCell(scanDate);
sheetOut.addCell(scanTime);
sheetOut.addCell(scanOffset);
sheetOut.addCell(scanStatus);
sheetOut.addCell(scanLoc);
sheetOut.addCell(scanComments);
currentRow++;
}
currentRow++;
}
// All sheets and cells added. Now write out the
workbook
workbookOut.write();
workbookOut.close();
sendMessage (req.getFrom(), outputStream.toByteArray());
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (MessagingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void sendMessage (String to, byte[] attachment) throws
MessagingException, IOException, InterruptedException {
Message message = buildMessage (to, attachment);
Transport.send(message);
}
private Message buildMessage (String to, byte[] data) throws
MessagingException, IOException, InterruptedException {
StringBuffer sb = new StringBuffer("Please find tracking data
attached.");
String subject = "Tracking Spreadsheet";
Properties props = new Properties();
Session session = Session.getDefaultInstance(props, null);
Message message = new MimeMessage(session);
message.setFrom(new InternetAddress("[email protected]",
"Shipment Tracking"));
message.setReplyTo(new Address[] {new InternetAddress("track@"
+ SystemProperty.applicationId.get() + ".appspotmail.com", "xxxxxx")});
message.addRecipient(Message.RecipientType.TO,
new InternetAddress(to, to));
message.setSubject(subject);
Multipart mp = new MimeMultipart();
String filename = URLEncoder.encode("TrackingOutput.xls",
"UTF-8");
MimeBodyPart attachmentBP = new MimeBodyPart();
attachmentBP.setFileName(filename);
attachmentBP.setDisposition(Part.ATTACHMENT);
DataSource src = new ByteArrayDataSource (data,
"application/x-ms-excel");
DataHandler handler = new DataHandler (src);
attachmentBP.setDataHandler(handler);
mp.addBodyPart(attachmentBP);
MimeBodyPart plainBody = new MimeBodyPart();
plainBody.setContent(sb.toString(), "text/plain");
plainBody.setFileName("plainbody.txt");
mp.addBodyPart(plainBody);
message.setText(sb.toString());
message.setContent(mp);
message.saveChanges();
return message;
}
}
On Nov 14, 2010, at 1:08 PM, Vikas Hazrati wrote:
> I have an application which is deployed on the Google App Engine. This
> application also works with the Google spreadsheet API to update a
> couple of spreadsheets.
>
> Once the user is done with changing the spreadsheet data, I want to
> email the resultant spreadsheet (not the link) to the manager.
>
> Is there a way to attach the spreadsheet as an attachment ?
>
> Since the app engine supports multi-part for attachment,
> http://code.google.com/appengine/docs/java/mail/usingjavamail.html#Multi_Part_Messages
> I guess I would need to get the content of the spreadsheet back as a
> byte []. How can I do that?
>
> Is there any other way to approach this problem?
>
> Regards | Vikas
> www.inphina.com
>
> --
> You received this message because you are subscribed to the Google Groups
> "Google App Engine for Java" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/google-appengine-java?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Google App Engine for Java" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/google-appengine-java?hl=en.
