“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:
Working with Excel is quite different from working with a regular text file. In addition to values, its cells can store complex formulas and data downloaded by links. Also, in a column with one data type, values of other types may occur. In case of any error in the cell, the information on the sheet continues to be available and only this cell will have the Error status. 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 with a mouse selected part of a table in Excel and copied it to another page, asking me a question - “Is it really so difficult to do the same, only with copying to the database? You are a Professional! ” After that conversation, for a while, I felt like a hamster who was given a drop of nicotine, which killed a horse. But time passed, and with the help of MSDN and the Internet, I became closer with MS Excel, and daily dances with a tambourine around him 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 made the user work only with them. Attempts were also made not to use MS Excel when working with the database, but they did not find understanding.
At the moment, to download and upload data from the database to Excel and from Excel to the database, the following are used:
In addition to the above methods of working with Excel, there are others, but they were not used to solve the tasks.
The tool is convenient, allows you to build reports using many different data sources and upload them to files of various formats. It supports uploading to Excel, integrated into MS Sharepoint, has a good report editor - MS Report Builder.
Allows you to work with * .xls and * .xlsx files. 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).
It is used for uploading to Excel in * .xlsx format (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 allows you to load and unload data in * .xlsx format. Its advantage over the Open XML SDK is a more user-friendly API and less laboriousness. It is much more convenient than the Open XML SDK. At the moment, it is used by the company in cases where it is not necessary to use the Simple API for XML (SAX).
The C # code examples turned out to be very large, so I decided to write the Conclusion before them.
In this article I wanted to share my experience with excel files, as 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 above tools, but in the end I decided that the code examples would be more useful.
Thanks to everyone who was able to read everything I wrote above!
The code is intentionally simplified to highlight only the essence. All examples were made in a test project, in order to show how you can work with this. Various nuances of programming and issues of error tolerance were not paid attention to it (according to the principle - “Do you need checkers, or go?”).
...
- Presentation of reports from a database in Excel format
- Collect information in the form of Excel files and upload it to Oracle
Issues:
Working with Excel is quite different from working with a regular text file. In addition to values, its cells can store complex formulas and data downloaded by links. Also, in a column with one data type, values of other types may occur. In case of any error in the cell, the information on the sheet continues to be available and only this cell will have the Error status. 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 with a mouse selected part of a table in Excel and copied it to another page, asking me a question - “Is it really so difficult to do the same, only with copying to the database? You are a Professional! ” After that conversation, for a while, I felt like a hamster who was given a drop of nicotine, which killed a horse. But time passed, and with the help of MSDN and the Internet, I became closer with MS Excel, and daily dances with a tambourine around him 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 made the user 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
- Firstly - no one requires replacing MS Excel with something else.
- Secondly - such attempts are perceived by others with bewilderment. In the best case, such “revolutionaries” are patted on the head and released home early, at worst they are escorted by a sympathetic look and shake their heads.
What have I come to:
At the moment, to download and upload data from the database to Excel and from Excel to the database, the following are used:
- Microsoft Reporting Services (upload 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 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. It supports uploading to Excel, integrated into MS Sharepoint, has a good report editor - MS Report Builder.
Microsoft Office Interop Excel
Allows you to work with * .xls and * .xlsx files. 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 for uploading to Excel in * .xlsx format (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 load and unload data in * .xlsx format. Its advantage over the Open XML SDK is a more user-friendly API and less laboriousness. It is much more convenient than the Open XML SDK. At the moment, it is used by the company in cases where it is not necessary to use the Simple API for XML (SAX).
Conclusion
The C # code examples turned out to be very large, so I decided to write the Conclusion before them.
In this article I wanted to share my experience with excel files, as 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 above tools, but in the end I decided that the code examples would be more useful.
Thanks to everyone who was able to read everything I wrote above!
Code examples
The code is intentionally simplified to highlight only the essence. All examples were made in a test project, in order to show how you can work with this. Various nuances of programming and issues of error tolerance were not paid attention to it (according to the principle - “Do you need checkers, or go?”).
Microsoft Office Interop Excel, loading data from an 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, saving data to the database (SAX)
private static void Main(string[] args)
{
OpenXMLSaveExcel.SaveDataToExcel(@"c:\temp\1\test2.xlsx", "Тестовый лист");
}
...
namespace ExcelTest
{
class BadChars
{
static Lazy ControlChars = new Lazy(() => 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
{
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 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 {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();
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();
writer = OpenXmlWriter.Create(worksheetPart);
var worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
var ns = new Dictionary();
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 {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();
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";
}
}
}