"My way" with MS Excel

In the company where I work, users work with data using MS Excel. The main repository of information is the Oracle database. From here it is required:

• Reporting from the database in Excel format
• Retrieve information in the form of Excel files and upload it to Oracle

Problems:

Working with Excel is quite different from working with a regular text file. In addition to the values, complex formulas and data loaded by links can be stored in its cells. Also in the column with one data type values ​​of other types may occur. In the event of any error in the cell, the information on the sheet continues to be available and only this cell will have the status Error. There are other things that make Excel a very flexible and unique product for the user and not easy for the developer.

Somehow there was a case when the head of the mouse selected part of the table in Excel and copied it to another page, asking me the question - “Is it really so difficult to do this only with copying to the base? You are a Professional! ”. After that conversation, for a while, I felt like a hamster, who was given a drop of nicotine that killed a horse. But as time went on, and with the help of MSDN and the Internet, I became closer with MS Excel, and daily dances with a tambourine around it gave their result.

There are many ways to read (write) data from (to) Excel, everyone has the right to choose the one that is most convenient for him, but in this article I decided to tell you about my way of working with it:
')
I started with Microsoft Excel ODBC Driver, used Microsoft Integration Services, wrote macros on VB. I also used templates with ready-made macros and forced the user to work only with them. Attempts were also made not to use MS Excel when working with the database, but they did not find understanding.

Attempts to avoid using MS Excel

• First of all, nobody needs replacing MS Excel with something else.
• Secondly, such attempts are perceived by others with bewilderment. At best, such “revolutionaries” are stroked on the head and released home early, at worst, they are accompanied with a sympathetic look and shake their heads.

What I came to:

At the moment, to download and upload data from the database to Excel and from Excel to the database are used:

• Microsoft Reporting Services (download only) Link
• Microsoft Office Interop Excel Link
• Microsoft Open XML SDK Link
• EPPlus Link

In addition to the above methods of working with Excel, there are others, but they were not used to solve the set tasks.

Microsoft Reporting Services

The tool is convenient, allows you to build reports using many different data sources and upload them to files of various formats. Supported uploading to Excel, integrated into MS Sharepoint, has a good report editor - MS Report Builder.

Microsoft Office Interop Excel

Allows you to work with files * .xls and * .xlsx. In the company where I work, it is used to load data from MS Excel 2003 files into the database. Also, this tool can extract data from files with the * .xlsx extension (Microsoft Office Open XML format).

Microsoft Open XML SDK

It is used to upload to Excel in the format * .xlsx (Microsoft Office Open XML). To ensure speed and support the ability to upload large amounts of data, work with Microsoft Office Open XML is carried out using the Simple API for XML (SAX) Link .

EPPlus

EPPlus allows you to upload and download data in the * .xlsx format. Its advantage over the Open XML SDK is a more friendly API and less labor intensive. It is much more convenient to use than the Open XML SDK. At the moment it is used in the company in those cases where it is not necessary to use the Simple API for XML (SAX).

Conclusion

The C # code samples were very large, so I decided to write the Conclusion in front of them.
In this article I wanted to share my experience with excel files, because In my opinion, this is not a completely trivial task and quite common. Initially, I wanted to describe in detail all the subtleties of the work of the tools listed above, but in the end I decided that code samples would be more useful.
Thanks to everyone who could read everything I wrote about above!

Code examples

The code is intentionally simplified in order to isolate only the essence. All the examples were made in a test project, in order to show how to work with it. Various aspects of programming and error tolerance did not receive attention in it (according to the principle - “Do you want to go, or go?”).

Microsoft Office Interop Excel, download data from Excel file

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, save data to database (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"; } } }