Azureの小ネタ (改)

~Azureネタを中心に、色々とその他の技術的なことなどを~

Azure上でExcelファイルを生成をしてみたり

こんばんは、statemachineです。MSDN フォーラムもにわかに活発になってきた今日この頃いかがお過ごしでしょうか。発端は、ここでの質問あったAzure上でのExcelファイルの生成について考えてみたいと思います。

Excelファイルの生成について

Excelファイル生成にはいくつか方法があって、一般的なのは、Excelコンポーネントを使ってExcelファイルを生成することです。お手軽な反面、Excelがインストールされていないとダメだとか、サーバサイドでの利用は推奨されていない*1とか、PIA地獄に陥るとか問題があるわけです。

あとは、3rdパーティ製の製品を使うとか、JavaでPOI使うとか逃げてもあるのですが、Office2007/2010も広まってきたことですし、Open XMLフォーマットで生成するのがいいのかなとということで、レッツトライ。

準備

Open XML SDK が必要で、現在 2.0となっているようです。以下からダウンロードして、SDKのみインストールします。

WebRoleプロジェクトを作成して、DocumentFormat.OpenXmlと、WindowsBaseを参照設定に追加。DocumentFormat.OpenXMLは、ローカルコピーをTrueに設定して、出力ディレクトリにコピーされるよう設定しておきます。

生成部分

Excelファイルの生成は以下のURLを参考にしつつ改変。従来のExcelのオブジェクトモデルと微妙に違っていて、Open XMLのドキュメント構造を知っていないとプログラミングできないんじゃないかとも思えます、ちょっと敷居は高そうです。前に調べたときには、Open XML SDKAPIをラップしてExcelのオブジェクトモデルに見せるようなソフトもあった気もしますが失念。興味がある方は調べてみてください。

とくに、Cellに文字列を入れるときは、文字列をShareするみたいで直感的ではありません。以下、入力された文字列をA1のセルにぶち込んで、xlsx形式のExcelを生成することができます。

    public class ExcelGen
    {
        public static void CreateSpreadsheetWorkbook(string filepath, string value)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            //// Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
            sheets.Append(sheet);


            // Get the SharedStringTablePart. If it does not exist, create a new one.
            SharedStringTablePart shareStringPart;
            if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
            {
                shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }

            int index = InsertSharedStringItem(value, shareStringPart);


            Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);
            cell.CellValue = new CellValue(index.ToString());
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }


        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }


        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;

        }
    }

UI部分

UI部分は、以下のようにTextBox/Button/Labelを配置し、TextBoxで入力された文字列を、生成するExcelファイルに埋め込んでみます。

以下は、ボタンのハンドラ部分。やっていることといえば、

  • BlobコンテナをPublicアクセスで初期化
  • ローカルストレージにExcelファイル生成。ファイル名は適当にGUIDで。
  • 生成したファイルをBlobにアップロード。
  • Context-Typeには、application/vnd.openxmlformats-officedocumentを設定する。
  • Labelにリンク表示して終わり。
       protected void Button1_Click(object sender, EventArgs e)
        {
            var ls = RoleEnvironment.GetLocalResource("ls");
            var account = CloudStorageAccount.Parse(RoleEnvironment.GetConfigurationSettingValue("Setting1"));
            var blobclient = account.CreateCloudBlobClient();

            var container = blobclient.GetContainerReference("sample");
            container.CreateIfNotExist();
            container.SetPermissions(new BlobContainerPermissions()
            {
                PublicAccess = BlobContainerPublicAccessType.Container
            });

            var excel = new FileInfo(Path.Combine(ls.RootPath, Guid.NewGuid() + ".xlsx"));

            ExcelGen.CreateSpreadsheetWorkbook(excel.FullName, this.TextBox1.Text);
            var blob = container.GetBlobReference(excel.Name);
            blob.UploadFile(excel.FullName);
           
            blob.Properties.ContentType = "application/vnd.openxmlformats-officedocument";
            blob.SetProperties();

            this.Label1.Text = string.Format("File generated = <a href=\"{0}\">{1}</a>", blob.Uri.AbsoluteUri, excel.Name);
        }

実行

それでは、Azure上に配置して実行です。(ローカルでも確認してね)
適当なメッセージを入力して、生成ボタンを押すとリンクが表示されます。

リンクをクリックすると、ダイアログが表示されるので、Excelを開きます。


すると、先ほど入力した文字列が埋め込まれたExcelになっているはずです。

まとめ

とりえあず、Azureのサーバ側でExcelファイルを生成してBlogにぶち込むというシナリオは達成できたので終わりです。あとは、直接Redirectする方法とか知りたいんですけど、ASP.NETはよく知らないので勉強しなきゃと。

*1:http://support.microsoft.com/kb/257757/ja