В компании, где я работаю, пользователи работают с данными с помощью MS Excel. Основным хранилищем информации является БД Oracle. Отсюда требуется:
Работа с Excel, довольно сильно отличается от работы с обычным текстовым файлом. В его ячейках помимо значений могут храниться сложные формулы и данные, загруженные по ссылкам. Так же в колонке с одним типом данных могут встречаться значения других типов. В случае какой-либо ошибки в ячейке информация на листе продолжает оставаться доступной и только у этой ячейки будет статус Error. Есть и другие вещи, которые делают Excel очень гибким и уникальным продуктом для пользователя и не простым для разработчика.
Как-то был случай, когда начальник мышкой выделял часть таблицы в Excel и копировал его на другую страницу, задавая мне вопрос – “Неужели так сложно сделать это же, только с копированием в базу? Ты же Профессионал!”. После того разговора я, какое-то время, чувствовал себя хомячком, которому дали каплю никотина, убившую лошадь. Но время шло, и с помощью MSDN и интернета я стал ближе с MS Excel, а ежедневные танцы с бубном вокруг него дали свой результат.
Существует множество способов чтения (записи) данных из (в) Excel, каждый вправе выбирать тот, который ему наиболее удобен, я же в этой статье решил рассказать вам о своём пути работы с ним:
Начинал я с Microsoft Excel ODBC Driver, использовал Microsoft Integration Services, писал макросы на VB. Так же использовал шаблоны с уже готовыми макросами и заставлял пользователя работать только с ними. Так же принимались попытки не использовать MS Excel при работе с базой, но они не нашли понимания.
На данный момент для загрузки и выгрузки данных из базы в Excel и из Excel в базу используются:
Помимо вышеперечисленных способов работы с Excel существуют и другие, но при решении поставленных задач они не применялись.
Средство удобное, позволяет строить отчёты, используя множество различных источников данных и выгружать их в файлы различных форматов. Поддерживается выгрузка в Excel, интегрировано в MS Sharepoint, обладает неплохим редактором отчетов – MS Report Builder.
Позволяет работать с файлами *.xls и *.xlsx. В компании, где я работаю, используется для загрузки данных из файлов MS Excel 2003 в базу. Так же данное средство может извлекать данные из файлов с расширением *.xlsx (формат Microsoft Office Open XML).
Применяется для выгрузки в Excel в формате *.xlsx (Microsoft Office Open XML). Для обеспечения быстродействия и поддержки возможности выгружать большие объёмы данных работа с Microsoft Office Open XML ведётся с помощью Simple API for XML (SAX) Ссылка.
EPPlus позволяет загружать и выгружать данные в формат *.xlsx. Его преимущество перед Open XML SDK – более дружественное API и меньшая трудоемкость. Он гораздо более удобен в работе нежели Open XML SDK. На данный момент он используется в компании в тех случаях, где не обязательно использовать Simple API for XML (SAX).
Примеры кода на C# получились очень большими, поэтому я решил Заключение написать перед ними.
В данной статье я хотел поделиться опытом работы с excel файлами, т.к. на мой взгляд, это не совсем тривиальная задача и довольно распространенная. Изначально я хотел описать в деталях все тонкости работы перечисленных выше инструментов, но в итоге решил, что примеры кода будут более полезны.
Спасибо всем, кто смог прочитать всё, о чём я писал выше!
Код намеренно упрощён, чтобы выделить только саму суть. Все примеры делались в тестовом проекте, с целью показать, как с этим можно работать. Различным нюансам программирования и вопросам устойчивости к ошибкам внимание в нём не уделялось (по принципу — “Вам шашечки, или ехать?”).
…
- Представление отчетов из базы данных в формате 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";
}
}
}