Tuesday, June 21, 2016

Read Zipcodes from Excel via filename or stream

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;


/*

Stream is handy for something like ASP.NET file upload where you can upload an XLSX file
and get the open stream in button click handler; e.g.

fileUploader.PostedFile.InputStream

*/

namespace Propane.Utility
{
    public class ExcelHelper
    {
        /// <summary>
        /// Get a list of zipcodes from Excel.  Zipcodes must be in the first column of the first sheet.
        /// </summary>
        /// <param name="excelStream">Excel file opened as a Stream</param>
        /// <returns>List<string></returns>
        public List<string> GetZipcodes(Stream excelStream)
        {
            List<string> zipcodes;

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(excelStream, false))
            {
                zipcodes = ReadZipcodes(spreadsheetDocument);
            }

            return zipcodes;
        }

        /// <summary>
        /// Get a list of zipcodes from Excel.  Zipcodes must be in the first column of the first sheet.
        /// </summary>
        /// <param name="excelFileName">Full path to Excel file</param>
        /// <returns>List<string></returns>
        public List<string> GetZipcodes(string excelFileName)
        {
            List<string> zipcodes;

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(excelFileName, false))
            {
                zipcodes = ReadZipcodes(spreadsheetDocument);
            }

            return zipcodes;
        }

        private List<string> ReadZipcodes(SpreadsheetDocument spreadsheetDocument)
        {
            List<string> zipcodes = new List<string>();

            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
            Regex regex = new Regex(@"(\d{5})");

            foreach (Row r in sheetData.Elements<Row>())
            {
                Cell c = r.Elements<Cell>().FirstOrDefault();
                if (c != null)
                {
                    if (c.CellValue != null)
                    {
                        Match m = regex.Match(c.CellValue.Text);
                        if (m.Success == true)
                            zipcodes.Add(c.CellValue.Text);
                    }
                }
            }

            return zipcodes;
        }
    }
}

No comments:

Post a Comment