Преобразуйте Excel Range в ADO.NET DataSet или DataTable и т.д.
Существует довольно распространенный метод псевдо-COUNTUNIQUE¹ на основе функций SUMPRODUCT и COUNTIF , которые выглядят следующим образом.
=SUMPRODUCT(1/COUNTIF(A2:A19, A2:A19&""))
Но это компенсирует условия. Чтобы добавить условия, вам нужно перейти на функцию COUNTIFS . Кроме того, как числитель, так и знаменатель операции деления должны быть скорректированы условиями и инверсией условий, чтобы вы вернули истинный счетчик без ошибок с помощью #DIV/0!.
=SUMPRODUCT(((C$2:C$19>=F2)*(C$2:C$19<=G2)*($B$2:$B$19=E2))/
(COUNTIFS(A$2:A$19, A$2:A$19&"", C$2:C$19, ">="&F2, C$2:C$19, "<="&G2, $B$2:$B$19, E2)+
(C$2:C$19G2)+($B$2:$B$19<>E2)))
Теперь, когда формула трудно поддерживать, существуют постоянные добавления и удаления к числу строк. Чтобы создать динамические диапазоны, мы можем INDEX каждый полный столбец и использовать MATCH , чтобы найти последнюю дату в качестве терминатора для каждого столбца.
=SUMPRODUCT(((C$2:INDEX(C:C, MATCH(1E+99,C:C ))>=F2)*(C$2:INDEX(C:C, MATCH(1E+99,C:C ))<=G2)*($B$2:INDEX(B:B, MATCH(1E+99,C:C ))=E2))/
(COUNTIFS(A$2:INDEX(A:A, MATCH(1E+99,C:C )), A$2:INDEX(A:A, MATCH(1E+99,C:C ))&"", C$2:INDEX(C:C, MATCH(1E+99,C:C )), ">="&F2, C$2:INDEX(C:C, MATCH(1E+99,C:C )), "<="&G2, B$2:INDEX(B:B, MATCH(1E+99,C:C )), E2)+
(C$2:INDEX(C:C, MATCH(1E+99,C:C ))G2)+($B$2:INDEX(B:B, MATCH(1E+99,C:C ))<>E2)))
Да, это похоже на беспорядок, но он динамически добавляет и вычитает строки, обработанные функцией SUMPRODUCT, так что эффективность вычислений превзойдена.
¹ Для объяснения того, как это работает, см. Count Unique с SUMPRODUCT () Breakdown .
4 ответа
Я не знаю о встроенной функции, но написать ее самостоятельно не составит труда. Псевдокод:
DataTable MakeTableFromRange(Range range)
{
table = new DataTable
for every column in range
{
add new column to table
}
for every row in range
{
add new datarow to table
for every column in range
{
table.cells[column, row].value = range[column, row].value
}
}
return table
}
Я сделал метод, который уже может взять , фильтровал , данные из Excel Taking фильтровали данные в Диапазон Преобразование формата
Worksheet sheet = null;
sheet = (Worksheet)context.cDocumentExcel.Sheets[requiredSheetName];
DataTable dt = new DataTable();
sheet.Activate();
sheet.UsedRange.Select();
List<Range> ranges = new List<Range>();
Range usedrange = sheet.UsedRange;
foreach (var oneRange in usedrange.SpecialCells(XlCellType.xlCellTypeVisible))
{
ranges.Add(oneRange);
}
dt = (_makeTableFromRange(ranges));
от Диапазона до Таблицы данных
private static DataTable _makeTableFromRange(List<Range> ranges)
{
var table = new DataTable();
foreach (var range in ranges)
{
while (table.Columns.Count < range.Column)
{
table.Columns.Add();
}
while (table.Rows.Count < range.Row)
{
table.Rows.Add();
}
table.Rows[range.Row - 1][range.Column - 1] = range.Value2;
}
//clean from empty rows
var filteredRows = table.Rows.Cast<DataRow>().
Where(row => !row.ItemArray.All(field => field is System.DBNull ||
string.Compare((field as string).Trim(), string.Empty) ==
0));
table = filteredRows.CopyToDataTable();
return table;
}
Я не знаю, какой тип данных у вас есть, но для данных excel, как показано в этой ссылке http://www.freeimagehosting.net/image.php?f8d4ef4173.png, вы можете использовать следующий код для загрузки в таблицу данных.
private void Form1_Load(object sender, EventArgs e)
{
try
{
DataTable sheetTable = loadSingleSheet(@"C:\excelFile.xls", "Sheet1$");
dataGridView1.DataSource = sheetTable;
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message, "");
}
}
private OleDbConnection returnConnection(string fileName)
{
return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
}
private DataTable loadSingleSheet(string fileName, string sheetName)
{
DataTable sheetData = new DataTable();
using (OleDbConnection conn = this.returnConnection(fileName))
{
conn.Open();
// retrieve the data using data adapter
OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
sheetAdapter.Fill(sheetData);
}
return sheetData;
}
Когда дело доходит до чтения / записи XLS-файлов Excel 2003, стоит взглянуть на NPOI . НПОИ спасает жизнь.
Думаю, вам придется перебирать диапазон и создавать DataRows, чтобы поместить их в DataTable.
Этот вопрос на StackOverflow предоставляет дополнительные ресурсы: