/branches/1.45/App_Code/ExcelExporter.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using Yogesh.ExcelXml;

using System.Data.Common;

using System.Data;

using System.IO;

using ICSharpCode.SharpZipLib.Zip;

using System.Text;



//TODO: Add another sheet with help for translators



/// <summary>

/// Exports selected forms and languages to an Excel XML spreadsheet

/// </summary>

public static class ExcelExporter

{

    /// <summary>

    /// Generates, then sends an XML spreadsheet to the browser based on the inputs on the previous page

    /// </summary>

	public static string ExportToExcel(string[] applicationIds, string[] selectedLanguages, string[] selectedBundles, bool zipOutput) {

        Database db = null;

        string error = string.Empty;

        try {

            db = new Database();

            db.OpenConnection();

            

            ExcelXmlWorkbook wb = new ExcelXmlWorkbook();

            List<int> addedIds = new List<int>();

            wb.Properties.Title = "ResourceBlender Resources (" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ")";

            using(Database.Command cmdApplication = db.CreateCommand()) 

            using(Database.Command cmdResources = db.CreateCommand()) {

                cmdApplication.CommandText = "SELECT displayname, defaultLanguage, languages FROM " + db.TablePrefix + "applications WHERE id = @p";

                cmdApplication.AddParameterWithValue("@p", "");

                cmdResources.AddParameterWithValue("@p", "");

                string applicationId = null,

                       applicationName = null,

                       defaultLanguage = null;

                string[] allLanguagesInApplication = null;

                int startingColumn = 1;

                List<string> selectedLanguagesInApplication = new List<string>(); // if just one list of languages is used, there are gaps for columns where a language from another application is not present in the current application

                XmlStyle greenBackground = new XmlStyle(),

                         greyBackground = new XmlStyle();

                greenBackground.Interior.Color = System.Drawing.Color.FromArgb(234, 241, 221);

                greenBackground.Interior.Pattern = Pattern.Solid;

                greyBackground.Interior.Color = System.Drawing.Color.FromArgb(240, 240, 240);

                greyBackground.Interior.Pattern = Pattern.Solid;

                greyBackground.Font.Color = System.Drawing.Color.FromArgb(130, 130, 130);

                for(int i=0;i<applicationIds.Length;i++) {

                    selectedLanguagesInApplication.Clear();

                    applicationId = applicationIds[i];

                    cmdApplication.Parameters["@p"].Value = applicationId;

                    cmdResources.Parameters["@p"].Value = applicationId;

                    using(DbDataReader reader = cmdApplication.ExecuteReader(CommandBehavior.SingleResult)) {

                        while(reader.Read()) {

                            applicationName = reader.GetString(0);

                            defaultLanguage = reader.GetString(1);

                            allLanguagesInApplication = reader.GetString(2).Trim().Split(',');

                        }

                        reader.Close();

                    }

                    if(applicationName == null || applicationName.Length == 0) {

                        throw new ArgumentNullException("Application not found");

                    }

                    selectedLanguagesInApplication.Add(defaultLanguage);



                    int rowIndex = 1;

                    Worksheet sheet = wb[i];

                    sheet.Name = applicationName;

                    Row row = sheet.AddRow();

                    int column = startingColumn + 1;

                    sheet[startingColumn, 0].Value = "Resource ID";

                    Cell headerCell = sheet[column++, 0];

                    headerCell.Value = defaultLanguage;

                    foreach(string lang in allLanguagesInApplication) {

                        if(selectedLanguages.Contains(lang)) {

                            if(lang != defaultLanguage) {

                                selectedLanguagesInApplication.Add(lang);

                                headerCell = sheet[column++, 0];

                                headerCell.Value = lang;

                            }

                        }

                    }

                    sheet.FreezeTopRows = 1;

                    if(selectedLanguagesInApplication.Count == 0) {

                        throw new ArgumentException("No languages found for appliication:" + applicationName);

                    }



                    //sheet.Table.Columns[startingColumn].Width = 200;

                    cmdResources.CommandText = new StringBuilder("SELECT DISTINCT e.resourceid, e.id, r.language, r.translation, r.translatorcomment, c.comment FROM ")

                                                                .Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ")

                                                                .Append(db.TablePrefix).Append("resourcecomments AS c ON c.resourceid = r.resourceid WHERE applicationid = @p AND language IN('")

                                                                .Append(Utils.Implode(selectedLanguagesInApplication, "','", false)).Append("') AND bundle IN('").Append(Utils.Implode(selectedBundles, "','", false)).Append("') ORDER BY e.id").ToString();

                    using(DbDataReader reader = cmdResources.ExecuteReader()) {

                        int lastIdRead = -1;

                        while(reader.Read()) {

                            int resourceId = int.Parse(reader["resourceid"].ToString());

                            Cell cell = null;

                            if(lastIdRead == -1) {

                                if(addedIds.Contains(resourceId)) {

                                    continue;

                                }

                                addedIds.Add(resourceId);

                                lastIdRead = resourceId;

                                cell = sheet[startingColumn, rowIndex];

                                cell.Value = resourceId;

                                cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");

                                cell.Style = greyBackground;

                            } else if(lastIdRead != resourceId) {

                                if(addedIds.Contains(resourceId)) {

                                    continue;

                                }

                                addedIds.Add(resourceId);

                                lastIdRead = resourceId;

                                cell = sheet[startingColumn, ++rowIndex];

                                cell.Value = resourceId;

                                cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");

                                cell.Style = greyBackground;

                            }

                            cell = sheet[selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1, rowIndex];

                            //System.Diagnostics.Debug.WriteLine(reader["language"].ToString()+"\t"+selectedLanguagesInApplication.IndexOf(reader["language"].ToString())+"\t"+selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1);

                            cell.Value = reader["translation"].ToString().Replace("<", "&lt;").Replace(">", "&gt;"); //HttpUtility.HtmlEncode(reader["translation"].ToString());

                            cell.Comment = reader["translatorcomment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");

                        }

                        reader.Close();

                    }

                    Range defaultColumn = new Range(sheet[startingColumn + 1, 0], sheet[startingColumn + 1, rowIndex]);

                    defaultColumn.Style = greenBackground;

                    Range headerCells = new Range(sheet[startingColumn, 0], sheet[column, 0]);

                    headerCells.Font.Bold = true;

                }

            }

            HttpResponse res = HttpContext.Current.Response;

            using(MemoryStream msXml  = new MemoryStream()) { // holding stream for workbook

                string fileName = "ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm");

                if(zipOutput) {

                    using(MemoryStream msZip = new MemoryStream()) { // output stream for zip

                        ZipOutputStream zipStream = new ZipOutputStream(msZip);

                        zipStream.SetLevel(9);



                        wb.Export(msXml);

                        byte[] buffer = msXml.ToArray();

                        msXml.Close();

                        ZipEntry zipEntry = new ZipEntry(fileName + ".xml");

                        zipEntry.DateTime = DateTime.Now;

                        zipEntry.Size = buffer.Length;

                        zipStream.PutNextEntry(zipEntry);

                        zipStream.Write(buffer, 0, buffer.Length);

                        zipStream.Finish();



                        res.Clear();

                        res.AddHeader("Content-Type", "binary/octet-stream");

                        res.AddHeader("Content-Length", zipStream.Length.ToString());

                        res.AddHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".zip\"; size=" + zipStream.Length.ToString());

                        res.Flush();



                        res.BinaryWrite(msZip.ToArray());

                        msZip.Close();

                    }

                } else {

                    wb.Export(msXml);

                    res.Clear();

                    res.AddHeader("Content-Type", "binary/octet-stream");

                    res.AddHeader("Content-Length", msXml.Length.ToString());

                    res.AddHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xml\"; size=" + msXml.Length.ToString()); // todo: include date in filename

                    msXml.WriteTo(res.OutputStream);

                }

                res.Flush();

                res.End();

            }

        } catch(Exception ex) {

            error = ex.Message + ex.StackTrace;

        } finally {

            if(db != null) {

                db.CloseConnection();

            }

        }

        return error;

    }

}