«My way» с MS Excel

В компании, где я работаю, пользователи работают с данными с помощью MS Excel. Основным хранилищем информации является БД Oracle. Отсюда требуется:

• Представление отчетов из базы данных в формате Excel
• Забирать информацию в виде Excel файлов и заливать её в Oracle

Проблематика:

Работа с Excel, довольно сильно отличается от работы с обычным текстовым файлом. В его ячейках помимо значений могут храниться сложные формулы и данные, загруженные по ссылкам. Так же в колонке с одним типом данных могут встречаться значения других типов. В случае какой-либо ошибки в ячейке информация на листе продолжает оставаться доступной и только у этой ячейки будет статус Error. Есть и другие вещи, которые делают Excel очень гибким и уникальным продуктом для пользователя и не простым для разработчика.

Как-то был случай, когда начальник мышкой выделял часть таблицы в Excel и копировал его на другую страницу, задавая мне вопрос – “Неужели так сложно сделать это же, только с копированием в базу? Ты же Профессионал!”. После того разговора я, какое-то время, чувствовал себя хомячком, которому дали каплю никотина, убившую лошадь. Но время шло, и с помощью MSDN и интернета я стал ближе с MS Excel, а ежедневные танцы с бубном вокруг него дали свой результат.

Существует множество способов чтения (записи) данных из (в) Excel, каждый вправе выбирать тот, который ему наиболее удобен, я же в этой статье решил рассказать вам о своём пути работы с ним:

Начинал я с Microsoft Excel ODBC Driver, использовал Microsoft Integration Services, писал макросы на VB. Так же использовал шаблоны с уже готовыми макросами и заставлял пользователя работать только с ними. Так же принимались попытки не использовать MS Excel при работе с базой, но они не нашли понимания.

Попытки уйти от использования MS Excel

• Во-первых – никто не требует замены MS Excel на что-то другое.
• Во-вторых — такие попытки воспринимаются окружающими с недоумением. В лучшем случае таких “революционеров” гладят по головке и отпускают домой пораньше, в худшем провожают сочувствующим взглядом и покачивают головой.

К чему я пришёл:

На данный момент для загрузки и выгрузки данных из базы в Excel и из Excel в базу используются:

• Microsoft Reporting Services (только для выгрузки) Ссылка
• Microsoft Office Interop Excel Ссылка
• Microsoft Open XML SDK Ссылка
• EPPlus Ссылка

Помимо вышеперечисленных способов работы с Excel существуют и другие, но при решении поставленных задач они не применялись.

Microsoft Reporting Services

Средство удобное, позволяет строить отчёты, используя множество различных источников данных и выгружать их в файлы различных форматов. Поддерживается выгрузка в Excel, интегрировано в MS Sharepoint, обладает неплохим редактором отчетов – MS Report Builder.

Microsoft Office Interop Excel

Позволяет работать с файлами *.xls и *.xlsx. В компании, где я работаю, используется для загрузки данных из файлов MS Excel 2003 в базу. Так же данное средство может извлекать данные из файлов с расширением *.xlsx (формат Microsoft Office Open XML).

Microsoft Open XML SDK

Применяется для выгрузки в Excel в формате *.xlsx (Microsoft Office Open XML). Для обеспечения быстродействия и поддержки возможности выгружать большие объёмы данных работа с Microsoft Office Open XML ведётся с помощью Simple API for XML (SAX) Ссылка.

EPPlus

EPPlus позволяет загружать и выгружать данные в формат *.xlsx. Его преимущество перед Open XML SDK – более дружественное API и меньшая трудоемкость. Он гораздо более удобен в работе нежели Open XML SDK. На данный момент он используется в компании в тех случаях, где не обязательно использовать Simple API for XML (SAX).

Заключение

Примеры кода на C# получились очень большими, поэтому я решил Заключение написать перед ними.
В данной статье я хотел поделиться опытом работы с excel файлами, т.к. на мой взгляд, это не совсем тривиальная задача и довольно распространенная. Изначально я хотел описать в деталях все тонкости работы перечисленных выше инструментов, но в итоге решил, что примеры кода будут более полезны.
Спасибо всем, кто смог прочитать всё, о чём я писал выше!

Примеры кода

Код намеренно упрощён, чтобы выделить только саму суть. Все примеры делались в тестовом проекте, с целью показать, как с этим можно работать. Различным нюансам программирования и вопросам устойчивости к ошибкам внимание в нём не уделялось (по принципу — “Вам шашечки, или ехать?”).

Microsoft Office Interop Excel, загрузка данных из Excel файла

private static void SaveDataToBase(object[,] arr)
        {
            // save data
        }

        private static object[,] loadCellByCell(int row, int maxColNum, _Worksheet osheet)
        {
            var list = new object[2, maxColNum + 1];
            for (int i = 1; i <= maxColNum; i++)
            {
                var RealExcelRangeLoc = osheet.Range[(object) osheet.Cells[row, i], (object) osheet.Cells[row, i]];
                object valarrCheck;
                try
                {
                    valarrCheck = RealExcelRangeLoc.Value[XlRangeValueDataType.xlRangeValueDefault];
                }
                catch
                {
                    valarrCheck = (object) RealExcelRangeLoc.Value2;
                }
                list[1, i] = valarrCheck;
            }
            return list;
        }

        public static void LoadExcelFiles()
        {
            Application ExcelObj = null;
            _Workbook ecelbook = null;
            try
            {
                ExcelObj = new Application();
                ExcelObj.DisplayAlerts = false;
                const string f = @"C:\Temp\1\test.xlsx";
                ecelbook = ExcelObj.Workbooks.Open(f, 0, true, 5, "", "", false, XlPlatform.xlWindows);
                var sheets = ecelbook.Sheets;
                var maxNumSheet = sheets.Count;
                for (int i = 1; i <= maxNumSheet; i++)
                {
                    var osheet = (_Worksheet) ecelbook.Sheets[i];
                    Range excelRange = osheet.UsedRange;

                    int maxColNum;
                    int lastRow;
                    try
                    {
                        maxColNum = excelRange.SpecialCells(XlCellType.xlCellTypeLastCell).Column;
                        lastRow = excelRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
                    }
                    catch
                    {
                        maxColNum = excelRange.Columns.Count;
                        lastRow = excelRange.Rows.Count;
                    }

                    for (int l = 1; l <= lastRow; l++)
                    {
                        Range RealExcelRangeLoc = osheet.Range[(object) osheet.Cells[l, 1], (object) osheet.Cells[l, maxColNum]];
                        object[,] valarr = null;
                        try
                        {
                            var valarrCheck = RealExcelRangeLoc.Value[XlRangeValueDataType.xlRangeValueDefault];
                            if (valarrCheck is object[,] || valarrCheck == null)
                                valarr = (object[,]) RealExcelRangeLoc.Value[XlRangeValueDataType.xlRangeValueDefault];
                        }
                        catch
                        {
                            valarr = loadCellByCell(l, maxColNum, osheet);
                        }
                        SaveDataToBase(valarr);
                    }
                }
            }
            finally
            {
                if (ecelbook != null)
                {
                    ecelbook.Close();
                    Marshal.ReleaseComObject(ecelbook);
                }
                if (ExcelObj != null) ExcelObj.Quit();
            }
        }

Open XML SDK, сохранение данных в базу (SAX)

private static void Main(string[] args)
{
     OpenXMLSaveExcel.SaveDataToExcel(@"c:\temp\1\test2.xlsx", "Тестовый лист");
}

…

namespace ExcelTest
{
    class BadChars
    {
        static Lazy<Regex> ControlChars = new Lazy<Regex>(() => new Regex("[\x00-\x1f]", RegexOptions.Compiled));

        private static string FixData_Replace(Match match)
        {
            if ((match.Value.Equals("\t")) || (match.Value.Equals("\n")) || (match.Value.Equals("\r")))
                return match.Value;

            return "&#" + ((int)match.Value[0]).ToString("X4") + ";";
        }

        public static string Fix(object data, MatchEvaluator replacer = null)
        {
            if (data == null) return null;
            string fixed_data;
            if (replacer != null) fixed_data = ControlChars.Value.Replace(data.ToString(), replacer);
            else fixed_data = ControlChars.Value.Replace(data.ToString(), FixData_Replace);
            return fixed_data;
        }
    }
    
    public class OraParameter
    {
        public string Name;
        public string ViewName;
        public OracleType type;
        public object Value;
    }
    
    public class BaseColumn
    {
        public string Name;
        public Type Type;
        public int size;
        public int colNumber;
    }

    public class OpenXMLSaveExcel
    {
        SpreadsheetDocument myDoc;
        WorksheetPart worksheetPart;
        WorkbookPart workbookPart;
        public OpenXmlWriter writer;
        OpenXmlWriter sheetWriter;

        public static void SaveDataToExcel(string filename, string sheetName)
        {
            var f = new OpenXMLSaveExcel();
            f.SaveExcel( filename, sheetName);
        }
        
        public void SaveExcel(string filename, string sheetName)
        {
            var lp = new List<OraParameter>
            {
                new OraParameter
                {
                    Name = "param1",
                    type = OracleType.VarChar,
                    Value = "тест значения параметра 1",
                    ViewName = "Параметр 1"
                },
                new OraParameter
                {
                    Name = "param2",
                    type = OracleType.Number,
                    Value = 245,
                    ViewName = "Параметр 2"
                }
            };

            CreateExcelFile(filename);
            SaveData(lp, "Тестовый отчет");
            CloseExcelFile(sheetName);
        }

        public void SaveData(List<OraParameter> parameters, string reportName)
        {
            if (!string.IsNullOrEmpty(reportName))
            {
                OpenRow(1);
                var c = new BaseColumn {Name = reportName, Type = typeof (string)};
                SaveCells(c, null);
                CloseRow();

                OpenRow(2);
                int i = 1;
                foreach (var p in parameters)
                {
                    c = new BaseColumn {Name = p.ViewName, Type = typeof (string)};
                    SaveCells(c, null);
                    i++;
                }
                CloseRow();

                OpenRow(3);
                i = 1;
                foreach (var p in parameters)
                {
                    c = new BaseColumn {Type = p.Value.GetType()};
                    SaveCells(c, p.Value.ToString());
                    i++;
                }
                CloseRow();
            }
        }

        private void OpenRow(int rowNum)
        {
            var oxa = new List<OpenXmlAttribute> {new OpenXmlAttribute("r", null, rowNum.ToString())};
            writer.WriteStartElement(new Row(), oxa);
        }

        private void CloseRow()
        {
            writer.WriteEndElement();
        }

        private void SaveCells(BaseColumn c, object value)
        {
            var oxa = new List<OpenXmlAttribute>();
            string exelType;
            
            if (value == null || value == DBNull.Value) exelType = "str";
            else exelType = getExcelType(c.Type);
            oxa.Add(exelType == "d" ? new OpenXmlAttribute("s", null, "1") : new OpenXmlAttribute("t", null, exelType));
            writer.WriteStartElement(new Cell(), oxa);
            saveCellValue(c, value, exelType);
            writer.WriteEndElement();
        }

        private void saveCellValue(BaseColumn c, object value, string exelType)
        {
            if (value == null)
            {
                    writer.WriteElement(new CellValue(c.Name));
            }
            else
            {
                var v = value is DBNull ? "" : value.ToString();
                switch (exelType)
                {
                    case "n": 
                        if (value is DBNull)
                            writer.WriteElement(new CellValue());
                        else
                            writer.WriteElement(new CellValue(v.Replace(",", ".")));
                        break;
                    case "d":
                            v = value is DBNull ? "" : Convert.ToDateTime(value).ToOADate().ToString();
                            writer.WriteElement(new CellValue(v));
                        break;
                    default:
                        writer.WriteElement(new CellValue(BadChars.Fix(v)));
                        break;
                }
            }
        }

        private void CloseExcelFile(string sheetName)
        {
            writer.WriteEndElement();
            writer.WriteEndElement(); 
            writer.Close();
            var sheetIds = myDoc.WorkbookPart.GetIdOfPart(worksheetPart);
            sheetWriter = OpenXmlWriter.Create(myDoc.WorkbookPart);
            sheetWriter.WriteStartElement(new Workbook());
            sheetWriter.WriteStartElement(new Sheets());

            sheetWriter.WriteElement(new Sheet()
            {
                        Name = sheetName,
                        SheetId = 1,
                        Id = sheetIds
            });

            sheetWriter.WriteEndElement();
            sheetWriter.WriteEndElement();
            sheetWriter.Close();
            myDoc.Close();
        }

        private void CreateExcelFile(string filename)
        {
            myDoc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
            workbookPart = myDoc.AddWorkbookPart();
            ApplyStylesheet(workbookPart);
            
            worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            writer = OpenXmlWriter.Create(worksheetPart);
            var worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            var ns = new Dictionary<string, string>();
            ns["r"] = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
            ns["mc"] = "http://schemas.openxmlformats.org/markup-compatibility/2006";
            ns["x14ac"] = "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac";

            var attr = new List<OpenXmlAttribute> {new OpenXmlAttribute("mc:Ignorable", null, "x14ac")};
            writer.WriteStartElement(worksheet, attr, ns);
            writer.WriteStartElement(new SheetData());
        }


        public static WorkbookStylesPart ApplyStylesheet(WorkbookPart workbookPart)
        {
            var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
            var stylesheet1 = new Stylesheet()
            {
                MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" }
            };
            stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            /*Шрифты*/
            Fonts fonts = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };

            Font font = new Font();
            FontSize fontSize = new FontSize() { Val = 11D };
            Color color = new Color() { Theme = (UInt32Value)1U };
            FontName fontName = new FontName() { Val = "Calibri" };
            FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = 2 };
            FontCharSet fontCharSet = new FontCharSet() { Val = 204 };
            FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };

            font.Append(fontSize);
            font.Append(color);
            font.Append(fontName);
            font.Append(fontFamilyNumbering);
            font.Append(fontCharSet);
            font.Append(fontScheme);

            fonts.Append(font);
            /*************/
            /*Заливка*/
            Fills fills = new Fills() { Count = (UInt32Value)1U };

            Fill fillNone = new Fill();
            PatternFill patternFillNone = new PatternFill() { PatternType = PatternValues.None };
            fillNone.Append(patternFillNone);

            fills.Append(fillNone);
            /*************/
            /*Границы*/
            Borders borders = new Borders() { Count = (UInt32Value)1U };

            Border border = new Border();
            LeftBorder leftBorder = new LeftBorder();
            RightBorder rightBorder = new RightBorder();
            TopBorder topBorder = new TopBorder();
            BottomBorder bottomBorder = new BottomBorder();
            DiagonalBorder diagonalBorder = new DiagonalBorder();

            border.Append(leftBorder);
            border.Append(rightBorder);
            border.Append(topBorder);
            border.Append(bottomBorder);
            border.Append(diagonalBorder);

            borders.Append(border);
            /*************/
            /*Форматы*/
            CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)3U };

            CellFormat stringCellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)0U,
                FontId = (UInt32Value)0U,
                FillId = (UInt32Value)0U,
                BorderId = (UInt32Value)0U,
                FormatId = (UInt32Value)0U
            };
            CellFormat dateCellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)14U,
                FontId = (UInt32Value)0U,
                FillId = (UInt32Value)0U,
                BorderId = (UInt32Value)0U,
                FormatId = (UInt32Value)0U,
                ApplyNumberFormat = true
            };
            CellFormat numberCellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)2U,
                FontId = (UInt32Value)0U,
                FillId = (UInt32Value)0U,
                BorderId = (UInt32Value)0U,
                FormatId = (UInt32Value)0U,
                ApplyNumberFormat = true
            };

            cellFormats.Append(stringCellFormat);
            cellFormats.Append(dateCellFormat);
            cellFormats.Append(numberCellFormat);
            /*************/
            stylesheet1.Append(fonts);
            stylesheet1.Append(fills);
            stylesheet1.Append(borders);
            stylesheet1.Append(cellFormats);

            workbookStylesPart.Stylesheet = stylesheet1;

            return workbookStylesPart;
        }

        private string getExcelType(Type Type)
        {
            if (Type == typeof(string)) return "str";
            if (Type == typeof(DateTime)) return "d";
            return "n";
        }
    }
}