Save Excel Workbook to Server using VSTO v3
Tadi sempat ketiduran dan terbangun tiba-tiba karena mimpi yang aneh. Akibatnya sekarang darah belum lancar naik ke otak sehingga belum bisa mikir yang berat2. Sambil nunggu booting otak selesai, nge-blog yang simple dulu ah...
Tadi sore sempat bikin fungsionalitas sederhana untuk Excel menggunakan VSTO v3. Ada keperluan untuk menyimpan current Excel workbook to web server via web services, which can be accessed directly from Excel Ribbon. Gue kasih bonus buat export workbook to PDF.
1. Create a Web Service contains one web method to receive and save Excel file to web server.
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class ExcelFileServices : System.Web.Services.WebService
{ private string uploadPath = "~/Uploads";
public ExcelFileServices()
{ if (!String.IsNullOrEmpty(ConfigurationManager.AppSettings["ExcelUploadPath"]))
{ uploadPath = ConfigurationManager.AppSettings["ExcelUploadPath"];
}
}
[WebMethod(Description = "Upload Excel File")]
public bool UploadExcelFile(string fileName, byte[] fileBytes)
{ //First, check the extension of uploaded file name
if ((fileName.Substring((fileName.Length - 3), 3).ToLower() != "xls")
&& (fileName.Substring((fileName.Length - 4), 4).ToLower() != "xlsx")
&& (fileName.Substring((fileName.Length - 3), 3).ToLower() != "pdf"))
{ //return false;
throw new SoapException("Uploaded file (" + fileName + ") is not allowed", SoapException.ServerFaultCode);
}
//File savedFile;
//StreamWriter streamWriter;
FileStream fileStream = null;
string savedFilePath = Server.MapPath(uploadPath + "/" + fileName);
try
{ fileStream = File.Open(savedFilePath, FileMode.Create, FileAccess.Write);
int fileBytesLength = fileBytes.Length;
fileStream.Write(fileBytes, 0, fileBytesLength);
fileStream.Flush();
fileStream.Close();
return true;
}
catch (UnauthorizedAccessException ex)
{ //return false;
throw new SoapException(ex.Message, SoapException.ServerFaultCode);
}
catch (Exception ex)
{ //return false;
throw new SoapException(ex.Message, SoapException.ServerFaultCode);
}
finally
{ if (fileStream != null)
{ fileStream.Close();
}
}
}
}
2. Create New Excel 2007 project, workbook or add-in, I use workbook. Add New Item -> choose Ribbon (Visual Designer). Add two button so they look like below.
3. Add Web Reference to previously created web service, name it: ExcelFileServices
4. Add SaveFileDialog control to ribbon, name it: saveFileDialog_ExportToPDF
5. Double click to each button, add following code.
private void button_ExportToPDF_Click(object sender, RibbonControlEventArgs e)
{ String filename = "Default filename.pdf";
saveFileDialog_ExportToPDF.DefaultExt = "*.PDF";
saveFileDialog_ExportToPDF.FileName = filename;
DialogResult result = saveFileDialog_ExportToPDF.ShowDialog();
if (result == DialogResult.OK)
{ filename = saveFileDialog_ExportToPDF.FileName;
System.Threading.ThreadPool.QueueUserWorkItem((delegate(object arg)
{ Globals.ThisWorkbook.ExportAsFixedFormat(
Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
filename,
Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
true, true, Type.Missing, Type.Missing, true, Type.Missing);
}), null);
}
}
private void button_SaveFileToServer_Click(object sender, RibbonControlEventArgs e)
{ //Get filename & format
string sFileName = (string)Globals.ThisWorkbook.Application.GetSaveAsFilename(
"Default filename",
"Excel 97-2003 Workbook (*.xls), *.xls, Excel Workbook (*.xlsx), " +
"*.xlsx, PDF (*.pdf), *.pdf",
Type.Missing, "Type file name and select extension",
Type.Missing).ToString();
//Save to selected format in local drive temporarily
if (sFileName.ToLower().EndsWith(".xlsx")) { Globals.ThisWorkbook.SaveAs(sFileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
Type.Missing, true, Type.Missing, Type.Missing, Type.Missing);
}
else if (sFileName.ToLower().EndsWith(".xls")) { Globals.ThisWorkbook.SaveAs(sFileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
Type.Missing, true, Type.Missing, Type.Missing, Type.Missing);
}
else if (sFileName.ToLower().EndsWith(".pdf")) { Globals.ThisWorkbook.ExportAsFixedFormat(
Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
sFileName,
Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
true, true, Type.Missing, Type.Missing, false, Type.Missing);
}
//Upload Thread
System.Threading.ThreadPool.QueueUserWorkItem((delegate(object arg)
{ System.IO.FileInfo fi = new System.IO.FileInfo(sFileName);
try
{ byte[] bytes = null;
using (System.IO.FileStream fs = fi.Open(System.IO.FileMode.Open,
System.IO.FileAccess.Read))
{ bytes = new byte[fs.Length];
int readBytes = fs.Read(bytes, 0, (int)fs.Length);
}
if (bytes == null)
{ return;
}
ExcelFileServices.ExcelFileServices srv = new ExcelFileServices.ExcelFileServices();
srv.UploadExcelFile(fi.Name, bytes);
}
catch (Exception ex)
{ MessageBox.Show(ex.Message, "File Saving Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return;
}
//try to delete
try
{ fi.Delete();
}
catch { }
MessageBox.Show("File is successfully saved to server", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}), null);
}
That's it. Mudah2an berhasil :) The code should be self describing.
Please note:
- I know, this code is not optimal. File sending via web service can be improved using WCF or MTOM in Web Services Enhancement (WSE). The sending process can be done byte by byte.
- To be able to export to PDF, you must install SaveAsPDFandXPS.exe that can be downloaded from Microsoft website (forget the link).
It's time to get back to work. Sepertinya otak gue udah selesai booting.