💱 🤹🏼 🛀🏾 MS Excelでの「私のやり方」 👩🏿‍🎓 👵 🛷

私が働いている会社では、ユーザーはMS Excelを使用してデータを操作しています。情報のメインリポジトリはOracleデータベースです。ここから必要です：

Excel形式のデータベースからのレポートの表示
Excelファイルの形式で情報を収集し、Oracleにアップロードします

問題点：

Excelでの作業は、通常のテキストファイルでの作業とはまったく異なります。値に加えて、そのセルには、リンクによってダウンロードされた複雑な数式とデータを格納できます。また、1つのデータ型の列では、他の型の値が発生する場合があります。セルにエラーが発生した場合、シートの情報は引き続き利用可能であり、このセルのみがエラーステータスになります。 Excelをユーザーにとって非常に柔軟でユニークな製品にし、開発者にとっては簡単ではないものが他にもあります。

どういうわけか、マウスで頭がExcelでテーブルの一部を選択して別のページにコピーし、質問をする場合がありました-「データベースにコピーするだけで、同じことをするのは本当に難しいですか？あなたはプロです！」その会話の後、しばらくの間、私は馬を殺したニコチンの滴を与えられたハムスターのように感じました。しかし時間が経ち、MSDNとインターネットの助けを借りて、私はMS Excelに近づき、彼の周りのタンバリンとの毎日のダンスが結果を出しました。

Excelから（に）データを読み取る（書き込む）方法はたくさんありますが、誰もが自分にとって最も便利なものを選択する権利がありますが、この記事では、その操作方法について説明することにしました。

私はMicrosoft Excel ODBCドライバーから始め、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

このツールは便利で、さまざまなデータソースを使用してレポートを作成し、さまざまな形式のファイルにアップロードできます。 MS Sharepointに統合されたExcelへのアップロードをサポートし、MS Report Builderという優れたレポートエディターを備えています。

Microsoft Office Interop Excel

* .xlsおよび* .xlsxファイルを操作できます。私が働いている会社では、MS Excel 2003ファイルからデータベースにデータをロードするために使用されています。また、このツールは* .xlsx拡張子（Microsoft Office Open XML形式）のファイルからデータを抽出できます。

Microsoft Open XML SDK

* .xlsx形式（Microsoft Office Open XML）でExcelにアップロードするために使用されます。速度を確保し、大量のデータをアップロードする機能をサポートするために、Microsoft Office Open XMLの操作はSimple API for XML（SAX） Linkを使用して実行されます。

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(); } }

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"; } } }

MS Excelでの「私のやり方」