반응형
Excel Open XML을 읽는 것은 빈 셀을 무시하는 것입니다.
저는 엑셀 시트를 데이터 테이블로 변환하기 위해 여기서 승인된 솔루션을 사용하고 있습니다.데이터가 "완벽한" 경우에는 문제가 없지만 데이터 중간에 빈 셀이 있으면 각 열에 잘못된 데이터가 들어 있는 것 같습니다.
제 생각에 이것은 아래 코드에서:
row.Descendants<Cell>().Count()
는 채워진 셀의 수(모든 열은 아님)이며 다음을 수행합니다.
GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
는 다음에 채워진 셀(해당 인덱스에 있는 것은 아님)을 찾는 것으로 보이므로 첫 번째 열이 비어 있고 ElementAt(0)를 호출하면 두 번째 열에 있는 값을 반환합니다.
다음은 전체 구문 분석 코드입니다.
DataRow tempRow = dt.NewRow();
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
if (tempRow[i].ToString().IndexOf("Latency issues in") > -1)
{
Console.Write(tempRow[i].ToString());
}
}
Excel은 null인 셀에 대한 값을 저장하지 않으므로 이는 타당합니다.Open XML SDK 2.0 Productivity Tool을 사용하여 파일을 열고 XML을 셀 수준까지 이동하면 해당 파일에 데이터가 있는 셀만 있는 것을 볼 수 있습니다.
이동할 셀 범위에 빈 데이터를 삽입하거나 셀을 건너뛰었는지 프로그래밍 방식으로 확인하고 인덱스를 적절하게 조정할 수 있습니다.
셀 참조 A1과 C1에 있는 문자열로 엑셀 문서 예시를 만들었습니다.그런 다음 Open XML Productivity Tool에서 Excel 문서를 열었더니 저장된 XML이 다음과 같습니다.
<x:row r="1" spans="1:3"
xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:c r="A1" t="s">
<x:v>0</x:v>
</x:c>
<x:c r="C1" t="s">
<x:v>1</x:v>
</x:c>
</x:row>
여기서 데이터가 첫 번째 행에 해당하고 해당 행에 대해 두 개의 셀 값의 데이터만 저장되는 것을 확인할 수 있습니다.저장된 데이터는 A1 및 C1에 해당하며 null 값을 가진 셀은 저장되지 않습니다.
필요한 기능을 얻으려면 위와 같이 셀을 가로질러 이동할 수 있지만 셀이 참조하는 값을 확인하고 셀을 건너뛰었는지 여부를 확인해야 합니다.이렇게 하려면 셀 참조에서 열 이름을 가져온 다음 해당 열 이름을 제로 기반 인덱스로 변환하는 두 가지 유틸리티 함수가 필요합니다.
private static List<char> Letters = new List<char>() { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', ' ' };
/// <summary>
/// Given a cell name, parses the specified cell to get the column name.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Column Name (ie. B)</returns>
public static string GetColumnName(string cellReference)
{
// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellReference);
return match.Value;
}
/// <summary>
/// Given just the column name (no row index), it will return the zero based column index.
/// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ).
/// A length of three can be implemented when needed.
/// </summary>
/// <param name="columnName">Column Name (ie. A or AB)</param>
/// <returns>Zero based index if the conversion was successful; otherwise null</returns>
public static int? GetColumnIndexFromName(string columnName)
{
int? columnIndex = null;
string[] colLetters = Regex.Split(columnName, "([A-Z]+)");
colLetters = colLetters.Where(s => !string.IsNullOrEmpty(s)).ToArray();
if (colLetters.Count() <= 2)
{
int index = 0;
foreach (string col in colLetters)
{
List<char> col1 = colLetters.ElementAt(index).ToCharArray().ToList();
int? indexValue = Letters.IndexOf(col1.ElementAt(index));
if (indexValue != -1)
{
// The first letter of a two digit column needs some extra calculations
if (index == 0 && colLetters.Count() == 2)
{
columnIndex = columnIndex == null ? (indexValue + 1) * 26 : columnIndex + ((indexValue + 1) * 26);
}
else
{
columnIndex = columnIndex == null ? indexValue : columnIndex + indexValue;
}
}
index++;
}
}
return columnIndex;
}
그런 다음 셀 위에서 반복하고 셀 참조가 열과 비교되는지 확인할 수 있습니다.인덱스. 이 값보다 작으면 tempRow에 빈 데이터를 추가하고, 그렇지 않으면 셀에 포함된 값을 읽습니다. (참고:아래 코드를 테스트하지 않았지만 일반적인 아이디어가 도움이 될 것입니다.):
DataRow tempRow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
if (columnIndex < cellColumnIndex)
{
do
{
tempRow[columnIndex] = //Insert blank data here;
columnIndex++;
}
while(columnIndex < cellColumnIndex);
}
tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell);
if (tempRow[i].ToString().IndexOf("Latency issues in") > -1)
{
Console.Write(tempRow[i].ToString());
}
columnIndex++;
}
여기 다른 답변에도 의존했던 Waylon의 답변을 약간 수정한 버전이 있습니다.클래스에서 그의 메소드를 캡슐화합니다.
나는 변했어요
IEnumerator<Cell> GetEnumerator()
로.
IEnumerable<Cell> GetRowCells(Row row)
클래스는 다음과 같습니다. 인스턴스화할 필요 없이 유틸리티 클래스 역할만 합니다.
public class SpreedsheetHelper
{
///<summary>returns an empty cell when a blank cell is encountered
///</summary>
public static IEnumerable<Cell> GetRowCells(Row row)
{
int currentCount = 0;
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in
row.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
string columnName = GetColumnName(cell.CellReference);
int currentColumnIndex = ConvertColumnNameToNumber(columnName);
for (; currentCount < currentColumnIndex; currentCount++)
{
yield return new DocumentFormat.OpenXml.Spreadsheet.Cell();
}
yield return cell;
currentCount++;
}
}
/// <summary>
/// Given a cell name, parses the specified cell to get the column name.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Column Name (ie. B)</returns>
public static string GetColumnName(string cellReference)
{
// Match the column name portion of the cell name.
var regex = new System.Text.RegularExpressions.Regex("[A-Za-z]+");
var match = regex.Match(cellReference);
return match.Value;
}
/// <summary>
/// Given just the column name (no row index),
/// it will return the zero based column index.
/// </summary>
/// <param name="columnName">Column Name (ie. A or AB)</param>
/// <returns>Zero based index if the conversion was successful</returns>
/// <exception cref="ArgumentException">thrown if the given string
/// contains characters other than uppercase letters</exception>
public static int ConvertColumnNameToNumber(string columnName)
{
var alpha = new System.Text.RegularExpressions.Regex("^[A-Z]+$");
if (!alpha.IsMatch(columnName)) throw new ArgumentException();
char[] colLetters = columnName.ToCharArray();
Array.Reverse(colLetters);
int convertedValue = 0;
for (int i = 0; i < colLetters.Length; i++)
{
char letter = colLetters[i];
int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
convertedValue += current * (int)Math.Pow(26, i);
}
return convertedValue;
}
}
이제 다음과 같은 방법으로 모든 행의 셀을 가져올 수 있습니다.
// skip the part that retrieves the worksheet sheetData
IEnumerable<Row> rows = sheetData.Descendants<Row>();
foreach(Row row in rows)
{
IEnumerable<Cell> cells = SpreedsheetHelper.GetRowCells(row);
foreach (Cell cell in cells)
{
// skip part that reads the text according to the cell-type
}
}
셀이 비어 있더라도 모든 셀이 포함됩니다.
여기에 의구다니입현은다의 IEnumerable원하는 대로 컴파일하고 유닛 테스트를 수행해야 합니다.
///<summary>returns an empty cell when a blank cell is encountered
///</summary>
public IEnumerator<Cell> GetEnumerator()
{
int currentCount = 0;
// row is a class level variable representing the current
// DocumentFormat.OpenXml.Spreadsheet.Row
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in
row.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
string columnName = GetColumnName(cell.CellReference);
int currentColumnIndex = ConvertColumnNameToNumber(columnName);
for ( ; currentCount < currentColumnIndex; currentCount++)
{
yield return new DocumentFormat.OpenXml.Spreadsheet.Cell();
}
yield return cell;
currentCount++;
}
}
여기에 의존하는 기능은 다음과 같습니다.
/// <summary>
/// Given a cell name, parses the specified cell to get the column name.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Column Name (ie. B)</returns>
public static string GetColumnName(string cellReference)
{
// Match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellReference);
return match.Value;
}
/// <summary>
/// Given just the column name (no row index),
/// it will return the zero based column index.
/// </summary>
/// <param name="columnName">Column Name (ie. A or AB)</param>
/// <returns>Zero based index if the conversion was successful</returns>
/// <exception cref="ArgumentException">thrown if the given string
/// contains characters other than uppercase letters</exception>
public static int ConvertColumnNameToNumber(string columnName)
{
Regex alpha = new Regex("^[A-Z]+$");
if (!alpha.IsMatch(columnName)) throw new ArgumentException();
char[] colLetters = columnName.ToCharArray();
Array.Reverse(colLetters);
int convertedValue = 0;
for (int i = 0; i < colLetters.Length; i++)
{
char letter = colLetters[i];
int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
convertedValue += current * (int)Math.Pow(26, i);
}
return convertedValue;
}
수업시간에 던져보고 한번 해보세요.
구현 보기:
Row[] rows = worksheet.GetFirstChild<SheetData>()
.Elements<Row>()
.ToArray();
string[] columnNames = rows.First()
.Elements<Cell>()
.Select(cell => GetCellValue(cell, document))
.ToArray();
HeaderLetters = ExcelHeaderHelper.GetHeaderLetters((uint)columnNames.Count());
if (columnNames.Count() != HeaderLetters.Count())
{
throw new ArgumentException("HeaderLetters");
}
IEnumerable<List<string>> cellValues = GetCellValues(rows.Skip(1), columnNames.Count(), document);
//Here you can enumerate through the cell values, based on the cell index the column names can be retrieved.
헤더 문자는 다음 클래스를 사용하여 수집됩니다.
private static class ExcelHeaderHelper
{
public static string[] GetHeaderLetters(uint max)
{
var result = new List<string>();
int i = 0;
var columnPrefix = new Queue<string>();
string prefix = null;
int prevRoundNo = 0;
uint maxPrefix = max / 26;
while (i < max)
{
int roundNo = i / 26;
if (prevRoundNo < roundNo)
{
prefix = columnPrefix.Dequeue();
prevRoundNo = roundNo;
}
string item = prefix + ((char)(65 + (i % 26))).ToString(CultureInfo.InvariantCulture);
if (i <= maxPrefix)
{
columnPrefix.Enqueue(item);
}
result.Add(item);
i++;
}
return result.ToArray();
}
}
도우미 방법은 다음과 같습니다.
private static IEnumerable<List<string>> GetCellValues(IEnumerable<Row> rows, int columnCount, SpreadsheetDocument document)
{
var result = new List<List<string>>();
foreach (var row in rows)
{
List<string> cellValues = new List<string>();
var actualCells = row.Elements<Cell>().ToArray();
int j = 0;
for (int i = 0; i < columnCount; i++)
{
if (actualCells.Count() <= j || !actualCells[j].CellReference.ToString().StartsWith(HeaderLetters[i]))
{
cellValues.Add(null);
}
else
{
cellValues.Add(GetCellValue(actualCells[j], document));
j++;
}
}
result.Add(cellValues);
}
return result;
}
private static string GetCellValue(Cell cell, SpreadsheetDocument document)
{
bool sstIndexedcell = GetCellType(cell);
return sstIndexedcell
? GetSharedStringItemById(document.WorkbookPart, Convert.ToInt32(cell.InnerText))
: cell.InnerText;
}
private static bool GetCellType(Cell cell)
{
return cell.DataType != null && cell.DataType == CellValues.SharedString;
}
private static string GetSharedStringItemById(WorkbookPart workbookPart, int id)
{
return workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id).InnerText;
}
이 솔루션은 공유 셀 항목(SST 인덱스 셀)을 처리합니다.
모두 좋은 예입니다.상관 관계 및 분석을 위해 모든 행, 셀, 값 및 제목을 추적해야 하기 때문에 사용하는 것입니다.
읽기 스프레드시트 방법은 xlxs 파일을 열고 각 워크시트, 행 및 열을 거칩니다.값이 참조된 문자열 테이블에 저장되므로 워크시트별 값도 명시적으로 사용합니다.사용되는 다른 클래스는 DSFunction 및 StaticVariables입니다.후자는 참조된 'crlf double'(쿼트더블 = "\u0022"; ) 및 'crlf'(crlf = "\u000D" + "\u000A";)와 같은 자주 사용되는 매개 변수 값을 보유합니다.
관련 DSFunction 메서드 GetIntColIndexForLetter가 아래에 포함되어 있습니다.(A, B, AA, ADE 등)와 같은 문자 이름에 해당하는 열 인덱스의 정수 값을 반환합니다.이 값은 매개 변수 'ncellcolref'와 함께 사용되어 생략된 열이 있는지 확인하고 누락된 각 열에 대해 빈 문자열 값을 입력합니다.
또한 목록 개체에 임시로 저장하기 전에 값을 정리합니다(바꾸기 방법 사용).
그런 다음 열 이름의 해시 테이블(사전)을 사용하여 서로 다른 워크시트에서 값을 추출하고 상관 관계를 분석하여 정규화된 값을 만든 다음 제품에 사용되는 개체를 생성하여 XML 파일로 저장합니다.이 중 어느 것도 표시되지 않았지만 이 접근 방식이 사용되는 이유입니다.
public static class DSFunction {
/// <summary>
/// Creates an integer value for a column letter name starting at 1 for 'a'
/// </summary>
/// <param name="lettstr">Column name as letters</param>
/// <returns>int value</returns>
public static int GetIntColIndexForLetter(string lettstr) {
string txt = "", txt1="";
int n1, result = 0, nbeg=-1, nitem=0;
try {
nbeg = (int)("a".ToCharArray()[0]) - 1; //1 based
txt = lettstr;
if (txt != "") txt = txt.ToLower().Trim();
while (txt != "") {
if (txt.Length > 1) {
txt1 = txt.Substring(0, 1);
txt = txt.Substring(1);
}
else {
txt1 = txt;
txt = "";
}
if (!DSFunction.IsNumberString(txt1, "real")) {
nitem++;
n1 = (int)(txt1.ToCharArray()[0]) - nbeg;
result += n1 + (nitem - 1) * 26;
}
else {
break;
}
}
}
catch (Exception ex) {
txt = ex.Message;
}
return result;
}
}
public static class Extractor {
public static string ReadSpreadsheet(string fileUri) {
string msg = "", txt = "", txt1 = "";
int i, n1, n2, nrow = -1, ncell = -1, ncellcolref = -1;
Boolean haveheader = true;
Dictionary<string, int> hashcolnames = new Dictionary<string, int>();
List<string> colvalues = new List<string>();
try {
if (!File.Exists(fileUri)) { throw new Exception("file does not exist"); }
using (SpreadsheetDocument ssdoc = SpreadsheetDocument.Open(fileUri, true)) {
var stringTable = ssdoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
foreach (Sheet sht in ssdoc.WorkbookPart.Workbook.Descendants<Sheet>()) {
nrow = 0;
foreach (Row ssrow in ((WorksheetPart)(ssdoc.WorkbookPart.GetPartById(sht.Id))).Worksheet.Descendants<Row>()) {
ncell = 0;
ncellcolref = 0;
nrow++;
colvalues.Clear();
foreach (Cell sscell in ssrow.Elements<Cell>()) {
ncell++;
n1 = DSFunction.GetIntColIndexForLetter(sscell.CellReference);
for (i = 0; i < (n1 - ncellcolref - 1); i++) {
if (nrow == 1 && haveheader) {
txt1 = "-missing" + (ncellcolref + 1 + i).ToString() + "-";
if (!hashcolnames.TryGetValue(txt1, out n2)) {
hashcolnames.Add(txt1, ncell - 1);
}
}
else {
colvalues.Add("");
}
}
ncellcolref = n1;
if (sscell.DataType != null) {
if (sscell.DataType.Value == CellValues.SharedString && stringTable != null) {
txt = stringTable.SharedStringTable.ElementAt(int.Parse(sscell.InnerText)).InnerText;
}
else if (sscell.DataType.Value == CellValues.String) {
txt = sscell.InnerText;
}
else txt = sscell.InnerText.ToString();
}
else txt = sscell.InnerText;
if (txt != "") txt1 = txt.ToLower().Trim(); else txt1 = "";
if (nrow == 1 && haveheader) {
txt1 = txt1.Replace(" ", "");
if (txt1 == "table/viewname") txt1 = "tablename";
else if (txt1 == "schemaownername") txt1 = "schemaowner";
else if (txt1 == "subjectareaname") txt1 = "subjectarea";
else if (txt1.StartsWith("column")) {
txt1 = txt1.Substring("column".Length);
}
if (!hashcolnames.TryGetValue(txt1, out n1)) {
hashcolnames.Add(txt1, ncell - 1);
}
}
else {
txt = txt.Replace(((char)8220).ToString(), "'"); //special "
txt = txt.Replace(((char)8221).ToString(), "'"); //special "
txt = txt.Replace(StaticVariables.quotdouble, "'");
txt = txt.Replace(StaticVariables.crlf, " ");
txt = txt.Replace(" ", " ");
txt = txt.Replace("<", "");
txt = txt.Replace(">", "");
colvalues.Add(txt);
}
}
}
}
}
}
catch (Exception ex) {
msg = "notok:" + ex.Message;
}
return msg;
}
}
문자 코드는 기본 26 인코딩이므로 오프셋으로 변환할 수 있습니다.
// Converts letter code (i.e. AA) to an offset
public int offset( string code)
{
var offset = 0;
var byte_array = Encoding.ASCII.GetBytes( code ).Reverse().ToArray();
for( var i = 0; i < byte_array.Length; i++ )
{
offset += (byte_array[i] - 65 + 1) * Convert.ToInt32(Math.Pow(26.0, Convert.ToDouble(i)));
}
return offset - 1;
}
이 함수를 사용하여 헤더 인덱스를 통과하는 행에서 셀을 추출할 수 있습니다.
public static Cell GetCellFromRow(Row r ,int headerIdx) {
string cellname = GetNthColumnName(headerIdx) + r.RowIndex.ToString();
IEnumerable<Cell> cells = r.Elements<Cell>().Where(x=> x.CellReference == cellname);
if (cells.Count() > 0)
{
return cells.First();
}
else {
return null;
}
}
public static string GetNthColumnName(int n)
{
string name = "";
while (n > 0)
{
n--;
name = (char)('A' + n % 26) + name;
n /= 26;
}
return name;
}
좋아요, 저는 이것에 대해 전문가는 아니지만 다른 대답들은 저에게 과잉 살상으로 보입니다. 그래서 제 해결책은 이렇습니다.
// Loop through each row in the spreadsheet, skipping the header row
foreach (var row in sheetData.Elements<Row>().Skip(1))
{
var i = 0;
string[] letters = new string[15] {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O" };
List<String> cellsList = new List<string>();
foreach (var cell in row.Elements<Cell>().ToArray())
{
while (cell.CellReference.ToString()[0] != Convert.ToChar(letters[i]))
{//accounts for multiple consecutive blank cells
cellsList.Add("");
i++;
}
cellsList.Add(cell.CellValue.Text);
i++;
}
string[] cells = cellsList.ToArray();
foreach(var cell in cellsList)
{
//display contents of cell, depending on the datatype you may need to call each of the cells manually
}
}
누군가 이것이 유용하다고 생각하기를 바랍니다!
이 질문에 대한 또 다른 답변을 올린 것에 대해 사과드리며, 여기 제가 사용한 코드가 있습니다.
워크시트의 맨 위에 빈 행이 있으면 OpenXML이 제대로 작동하지 않는 문제가 있었습니다.행이 0개이고 열이 0개인 데이터 테이블만 반환하는 경우도 있습니다.아래 코드는 이것과 다른 모든 워크시트에 적용됩니다.
제 코드를 어떻게 부르실지는 다음과 같습니다.파일 이름과 워크시트의 이름을 입력하면 다음과 같습니다.
DataTable dt = OpenXMLHelper.ExcelWorksheetToDataTable("C:\\SQL Server\\SomeExcelFile.xlsx", "Mikes Worksheet");
코드 자체는 다음과 같습니다.
public class OpenXMLHelper
{
// A helper function to open an Excel file using OpenXML, and return a DataTable containing all the data from one
// of the worksheets.
//
// We've had lots of problems reading in Excel data using OLEDB (eg the ACE drivers no longer being present on new servers,
// OLEDB not working due to security issues, and blatantly ignoring blank rows at the top of worksheets), so this is a more
// stable method of reading in the data.
//
public static DataTable ExcelWorksheetToDataTable(string pathFilename, string worksheetName)
{
DataTable dt = new DataTable(worksheetName);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathFilename, false))
{
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName).FirstOrDefault();
if (theSheet == null)
throw new Exception("Couldn't find the worksheet: " + worksheetName);
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart = (WorksheetPart)(document.WorkbookPart.GetPartById(theSheet.Id));
Worksheet workSheet = wsPart.Worksheet;
string dimensions = workSheet.SheetDimension.Reference.InnerText; // Get the dimensions of this worksheet, eg "B2:F4"
int numOfColumns = 0;
int numOfRows = 0;
CalculateDataTableSize(dimensions, ref numOfColumns, ref numOfRows);
System.Diagnostics.Trace.WriteLine(string.Format("The worksheet \"{0}\" has dimensions \"{1}\", so we need a DataTable of size {2}x{3}.", worksheetName, dimensions, numOfColumns, numOfRows));
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
string[,] cellValues = new string[numOfColumns, numOfRows];
int colInx = 0;
int rowInx = 0;
string value = "";
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
// Iterate through each row of OpenXML data, and store each cell's value in the appropriate slot in our [,] string array.
foreach (Row row in rows)
{
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
// *DON'T* assume there's going to be one XML element for each column in each row...
Cell cell = row.Descendants<Cell>().ElementAt(i);
if (cell.CellValue == null || cell.CellReference == null)
continue; // eg when an Excel cell contains a blank string
// Convert this Excel cell's CellAddress into a 0-based offset into our array (eg "G13" -> [6, 12])
colInx = GetColumnIndexByName(cell.CellReference); // eg "C" -> 2 (0-based)
rowInx = GetRowIndexFromCellAddress(cell.CellReference)-1; // Needs to be 0-based
// Fetch the value in this cell
value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
cellValues[colInx, rowInx] = value;
}
}
// Copy the array of strings into a DataTable.
// We don't (currently) make any attempt to work out which columns should be numeric, rather than string.
for (int col = 0; col < numOfColumns; col++)
dt.Columns.Add("Column_" + col.ToString());
for (int row = 0; row < numOfRows; row++)
{
DataRow dataRow = dt.NewRow();
for (int col = 0; col < numOfColumns; col++)
{
dataRow.SetField(col, cellValues[col, row]);
}
dt.Rows.Add(dataRow);
}
#if DEBUG
// Write out the contents of our DataTable to the Output window (for debugging)
string str = "";
for (rowInx = 0; rowInx < maxNumOfRows; rowInx++)
{
for (colInx = 0; colInx < maxNumOfColumns; colInx++)
{
object val = dt.Rows[rowInx].ItemArray[colInx];
str += (val == null) ? "" : val.ToString();
str += "\t";
}
str += "\n";
}
System.Diagnostics.Trace.WriteLine(str);
#endif
return dt;
}
}
private static void CalculateDataTableSize(string dimensions, ref int numOfColumns, ref int numOfRows)
{
// How many columns & rows of data does this Worksheet contain ?
// We'll read in the Dimensions string from the Excel file, and calculate the size based on that.
// eg "B1:F4" -> we'll need 6 columns and 4 rows.
//
// (We deliberately ignore the top-left cell address, and just use the bottom-right cell address.)
try
{
string[] parts = dimensions.Split(':'); // eg "B1:F4"
if (parts.Length != 2)
throw new Exception("Couldn't find exactly *two* CellAddresses in the dimension");
numOfColumns = 1 + GetColumnIndexByName(parts[1]); // A=1, B=2, C=3 (1-based value), so F4 would return 6 columns
numOfRows = GetRowIndexFromCellAddress(parts[1]);
}
catch
{
throw new Exception("Could not calculate maximum DataTable size from the worksheet dimension: " + dimensions);
}
}
public static int GetRowIndexFromCellAddress(string cellAddress)
{
// Convert an Excel CellReference column into a 1-based row index
// eg "D42" -> 42
// "F123" -> 123
string rowNumber = System.Text.RegularExpressions.Regex.Replace(cellAddress, "[^0-9 _]", "");
return int.Parse(rowNumber);
}
public static int GetColumnIndexByName(string cellAddress)
{
// Convert an Excel CellReference column into a 0-based column index
// eg "D42" -> 3
// "F123" -> 5
var columnName = System.Text.RegularExpressions.Regex.Replace(cellAddress, "[^A-Z_]", "");
int number = 0, pow = 1;
for (int i = columnName.Length - 1; i >= 0; i--)
{
number += (columnName[i] - 'A' + 1) * pow;
pow *= 26;
}
return number - 1;
}
}
ClosedXML을 사용합니다.OpenX 대신 ExcelML:
public DataTable ImportTable(DataTable dt, string FileName)
{
Statics.currentProgressValue = 0;
Statics.maxProgressValue = 100;
Statics.cancelProgress = false;
try
{
bool fileExist = File.Exists(FileName);
if (fileExist)
{
using (XLWorkbook workBook = new XLWorkbook(FileName))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
var rowCount = workSheet.RangeUsed().RowCount();
if (rowCount > 0)
{
var colCount = workSheet.Row(1).CellsUsed().Count();
if (dt.Columns.Count < colCount)
throw new Exception($"Expects at least {dt.Columns.Count} columns.");
//Loop through the Worksheet rows.
Statics.maxProgressValue = rowCount;
for (int i = 1; i < rowCount; i++)
{
Statics.currentProgressValue += 1;
dt.Rows.Add();
for (int j = 2; j < dt.Columns.Count; j++)
{
var cell = (workSheet.Rows().ElementAt(i).Cell(j));
if (!string.IsNullOrEmpty(cell.Value.ToString()))
dt.Rows[i - 1][j] = cell.Value.ToString().Trim();
else
dt.Rows[i - 1][j] = "";
}
if (Statics.cancelProgress == true)
break;
}
}
return dt;
}
}
}
catch (Exception ex)
{
Statics.cancelProgress = true;
throw new Exception("Error exporting data." +
Environment.NewLine + ex.Message);
}
return dt;
}
아무라의 답변에서 서브루틴을 최적화하여 Regex의 필요성을 제거하는 것을 거부할 수 없습니다.
두 번째 기능은 셀 참조(C3) 또는 열 이름(C)을 허용할 수 있기 때문에 첫 번째 기능은 실제로 필요하지 않습니다(그래도 좋은 도우미 기능).인덱스도 단일 기반입니다(Excel과 시각적으로 일치하도록 행에 대해 단일 기반을 사용했기 때문에).
/// <summary>
/// Given a cell name, return the cell column name.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Column Name (ie. B)</returns>
/// <exception cref="ArgumentOutOfRangeException">cellReference</exception>
public static string GetColumnName(string cellReference)
{
// Advance from L to R until a number, then return 0 through previous position
//
for (int lastCharPos = 0; lastCharPos <= 3; lastCharPos++)
if (Char.IsNumber(cellReference[lastCharPos]))
return cellReference.Substring(0, lastCharPos);
throw new ArgumentOutOfRangeException("cellReference");
}
/// <summary>
/// Return one-based column index given a cell name or column name
/// </summary>
/// <param name="columnNameOrCellReference">Column Name (ie. A, AB3, or AB44)</param>
/// <returns>One based index if the conversion was successful; otherwise null</returns>
public static int GetColumnIndexFromName(string columnNameOrCellReference)
{
int columnIndex = 0;
int factor = 1;
for (int pos = columnNameOrCellReference.Length - 1; pos >= 0; pos--) // R to L
{
if (Char.IsLetter(columnNameOrCellReference[pos])) // for letters (columnName)
{
columnIndex += factor * ((columnNameOrCellReference[pos] - 'A') + 1);
factor *= 26;
}
}
return columnIndex;
}
열 수를 미리 알 수 있는 또 다른 구현이 추가되었습니다.
/// <summary>
/// Gets a list cells that are padded with empty cells where necessary.
/// </summary>
/// <param name="numberOfColumns">The number of columns expected.</param>
/// <param name="cells">The cells.</param>
/// <returns>List of padded cells</returns>
private static IList<Cell> GetPaddedCells(int numberOfColumns, IList<Cell> cells)
{
// Only perform the padding operation if existing column count is less than required
if (cells.Count < numberOfColumns - 1)
{
IList<Cell> padded = new List<Cell>();
int cellIndex = 0;
for (int paddedIndex = 0; paddedIndex < numberOfColumns; paddedIndex++)
{
if (cellIndex < cells.Count)
{
// Grab column reference (ignore row) <seealso cref="https://stackoverflow.com/a/7316298/674776"/>
string columnReference = new string(cells[cellIndex].CellReference.ToString().Where(char.IsLetter).ToArray());
// Convert reference to index <seealso cref="https://stackoverflow.com/a/848552/674776"/>
int indexOfReference = columnReference.ToUpper().Aggregate(0, (column, letter) => (26 * column) + letter - 'A' + 1) - 1;
// Add padding cells where current cell index is less than required
while (indexOfReference > paddedIndex)
{
padded.Add(new Cell());
paddedIndex++;
}
padded.Add(cells[cellIndex++]);
}
else
{
// Add padding cells when passed existing cells
padded.Add(new Cell());
}
}
return padded;
}
else
{
return cells;
}
}
다음을 사용하여 통화:
IList<Cell> cells = GetPaddedCells(38, row.Descendants<Cell>().ToList());
여기서 38은 필수 열 수입니다.
빈 셀을 읽기 위해 행 리더 외부에 할당된 "CN"이라는 변수를 사용하고 있으며, 루프하는 동안 각 셀을 읽은 후 컬럼 인덱스가 증가하므로 변수에서 열 인덱스가 더 큰지 여부를 확인하고 있습니다.만약 이것이 일치하지 않는다면, 저는 제가 원하는 값으로 제 칼럼을 채우고 있습니다.이것이 제가 빈 셀을 제가 존경하는 열 값으로 따라잡기 위해 사용한 속임수입니다.코드는 다음과 같습니다.
public static DataTable ReadIntoDatatableFromExcel(string newFilePath)
{
/*Creating a table with 20 columns*/
var dt = CreateProviderRvenueSharingTable();
try
{
/*using stream so that if excel file is in another process then it can read without error*/
using (Stream stream = new FileStream(newFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
var workbookPart = spreadsheetDocument.WorkbookPart;
var workbook = workbookPart.Workbook;
/*get only unhide tabs*/
var sheets = workbook.Descendants<Sheet>().Where(e => e.State == null);
foreach (var sheet in sheets)
{
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
/*Remove empty sheets*/
List<Row> rows = worksheetPart.Worksheet.Elements<SheetData>().First().Elements<Row>()
.Where(r => r.InnerText != string.Empty).ToList();
if (rows.Count > 1)
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
int i = 0;
int BTR = 0;/*Break the reader while empty rows are found*/
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
/*ignoring first row with headers and check if data is there after header*/
if (i < 2)
{
i++;
continue;
}
reader.ReadFirstChild();
DataRow row = dt.NewRow();
int CN = 0;
if (reader.ElementType == typeof(Cell))
{
do
{
Cell c = (Cell)reader.LoadCurrentElement();
/*reader skipping blank cells so data is getting worng in datatable's rows according to header*/
if (CN != 0)
{
int cellColumnIndex =
ExcelHelper.GetColumnIndexFromName(
ExcelHelper.GetColumnName(c.CellReference));
if (cellColumnIndex < 20 && CN < cellColumnIndex - 1)
{
do
{
row[CN] = string.Empty;
CN++;
} while (CN < cellColumnIndex - 1);
}
}
/*stopping execution if first cell does not have any value which means empty row*/
if (CN == 0 && c.DataType == null && c.CellValue == null)
{
BTR++;
break;
}
string cellValue = GetCellValue(c, workbookPart);
row[CN] = cellValue;
CN++;
/*if any text exists after T column (index 20) then skip the reader*/
if (CN == 20)
{
break;
}
} while (reader.ReadNextSibling());
}
/*reader skipping blank cells so fill the array upto 19 index*/
while (CN != 0 && CN < 20)
{
row[CN] = string.Empty;
CN++;
}
if (CN == 20)
{
dt.Rows.Add(row);
}
}
/*escaping empty rows below data filled rows after checking 5 times */
if (BTR > 5)
break;
}
reader.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
private static string GetCellValue(Cell c, WorkbookPart workbookPart)
{
string cellValue = string.Empty;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi =
workbookPart.SharedStringTablePart.SharedStringTable
.Elements<SharedStringItem>()
.ElementAt(int.Parse(c.CellValue.InnerText));
if (ssi.Text != null)
{
cellValue = ssi.Text.Text;
}
}
else
{
if (c.CellValue != null)
{
cellValue = c.CellValue.InnerText;
}
}
return cellValue;
}
public static int GetColumnIndexFromName(string columnNameOrCellReference)
{
int columnIndex = 0;
int factor = 1;
for (int pos = columnNameOrCellReference.Length - 1; pos >= 0; pos--) // R to L
{
if (Char.IsLetter(columnNameOrCellReference[pos])) // for letters (columnName)
{
columnIndex += factor * ((columnNameOrCellReference[pos] - 'A') + 1);
factor *= 26;
}
}
return columnIndex;
}
public static string GetColumnName(string cellReference)
{
/* Advance from L to R until a number, then return 0 through previous position*/
for (int lastCharPos = 0; lastCharPos <= 3; lastCharPos++)
if (Char.IsNumber(cellReference[lastCharPos]))
return cellReference.Substring(0, lastCharPos);
throw new ArgumentOutOfRangeException("cellReference");
}
코드 작동 대상:
- 이 코드는 빈 셀을 읽습니다.
- 읽기가 완료된 후 빈 행을 건너뜁니다.
- 시트를 처음부터 오름차순으로 읽습니다.
- Excel 파일이 다른 프로세스에서 사용 중인 경우 OpenXML은 여전히 이 파일을 읽습니다.
여기 제 해결책이 있습니다.행 끝에 있는 누락된 필드에서 위의 내용이 제대로 작동하지 않는 것 같습니다.
Excel 시트의 첫 번째 행에 모든 열이 있다고 가정하고(헤더를 통해), 행당 예상되는 열 수를 파악합니다(행 == 1).그런 다음 데이터 행을 반복합니다(행 > 1).누락된 셀을 처리하는 핵심은 알려진 수의 열 셀과 처리할 현재 행이 전달되는 getRowCells 메서드에 있습니다.
int columnCount = worksheetPart.Worksheet.Descendants<Row>().Where(r => r.RowIndex == 1).FirstOrDefault().Descendants<Cell>().Count();
IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>().Where(r => r.RowIndex > 1);
List<List<string>> docData = new List<List<string>>();
foreach (Row row in rows)
{
List<Cell> cells = getRowCells(columnCount, row);
List<string> rowData = new List<string>();
foreach (Cell cell in cells)
{
rowData.Add(getCellValue(workbookPart, cell));
}
docData.Add(rowData);
}
방법 getRowCells에는 현재 26개 미만의 열이 있는 시트(행)만 지원할 수 있다는 제한이 있습니다.알려진 열 카운트를 기반으로 하는 루프는 결측 열(셀)을 찾는 데 사용됩니다.발견되면 새 셀 값이 셀 집합에 삽입되고 새 셀의 기본값은 'null' 대신 ""입니다.그런 다음 수정된 셀 컬렉션이 반환됩니다.
private static List<Cell> getRowCells(int columnCount, Row row)
{
const string COLUMN_LETTERS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (columnCount > COLUMN_LETTERS.Length)
{
throw new ArgumentException(string.Format("Invalid columnCount ({0}). Cannot be greater than {1}",
columnCount, COLUMN_LETTERS.Length));
}
List<Cell> cells = row.Descendants<Cell>().ToList();
for (int i = 0; i < columnCount; i++)
{
if (i < cells.Count)
{
string cellColumnReference = cells.ElementAt(i).CellReference.ToString();
if (cellColumnReference[0] != COLUMN_LETTERS[i])
{
cells.Insert(i, new Cell() { CellValue = new CellValue("") }); }
}
else
{
cells.Insert(i, new Cell() { CellValue = new CellValue("") });
}
}
return cells;
}
private static string getCellValue(WorkbookPart workbookPart, Cell cell)
{
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
string value = (cell.CellValue != null) ? cell.CellValue.InnerXml : string.Empty;
if ((cell.DataType != null) && (cell.DataType.Value == CellValues.SharedString))
{
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
else
{
return value;
}
}
다음 코드로 성공적으로 실행됩니다.
string filePath = "test.xlsx"//your file path
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Create a new DataTable.
DataTable dt = new DataTable();
//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
//for (IXLCell cell in row.Cells())
for (int j = 1; j <= dt.Columns.Count; j++)
{
if (string.IsNullOrEmpty(row.Cell(j).Value.ToString()))
dt.Rows[dt.Rows.Count - 1][i] = "";
else
dt.Rows[dt.Rows.Count - 1][i] =
row.Cell(j).Value.ToString();
i++;
}
}
}
}
언급URL : https://stackoverflow.com/questions/3837981/reading-excel-open-xml-is-ignoring-blank-cells
반응형
'programing' 카테고리의 다른 글
| 내가 개발 중인 iOS 애플리케이션이 워크스테이션에서 장치에 생성한 파일을 탐색하시겠습니까? (0) | 2023.05.06 |
|---|---|
| PostgreSQL에서 테이블의 목록 열 이름과 데이터 유형을 가져오는 방법은 무엇입니까? (0) | 2023.05.06 |
| Postgre를 합니까?SQL에서 "어센틱하지 않은" 데이터 정렬을 지원합니까? (0) | 2023.05.01 |
| 새 콘센트 연결을 삽입할 수 없습니다.명명된 클래스에 대한 정보를 찾을 수 없습니다. (0) | 2023.05.01 |
| 벡터 이미지는 Xcode(예: PDF 파일)에서 어떻게 작동합니까? (0) | 2023.05.01 |