在c#中读取Excel,其中一些列为空 [英] Reading Excel in c# where some columns are empty
问题描述
我有这样的Excel模板
I have an Excel template like this
我有一些阅读这个问题(我不能使用第三方库)。我的解决方案:
and I have some problems reading this (I can't use 3rd-party libraries). My solution:
public partial class CaricaDocumento : System.Web.UI.Page
{
static string HDR; // "Yes" indicates that the first row contains column names, not data
Regex regex = new Regex("([0-9]+)(.*)");
protected void Page_Load(object sender, EventArgs e)
{
string ind = "C:\\My_Template.xlsx";
string sheetName = "Page1";
DataTable dt = FromXLSX(ind, sheetName, true);
DataToView(dt);
}
// Bind data to the page
private void DataToView(DataTable dt)
{
LblFattura.Text = GetValue("AO10", dt);
LblDataFattura.Text = GetValue("AX10", dt);
LblCognomeOrRagioneSociale.Text = GetValue("B18", dt);
LblNome.Text = GetValue("AB18", dt);
}
// return the value from the cell, indicate a code like "A1", "B3", "AO10"...
public string GetValue(string codeCell, DataTable dt)
{
string[] substrings = regex.Split(codeCell);
string letterString = substrings[0]; // 'A' or 'B' ... 'AZ' ...
int letter = ColumnLetterToNumber(letterString); // transform the letter in a column index
int num = 1;
if (HDR == "Yes")
num = 2;
// if the first row is an header, do -2
// if the first row is a simple data row, do -1
int number = Int32.Parse(substrings[1]) - num; // the right row index
return dt.Rows[number][letter].ToString();
}
// transform the letter in a column index
public static int ColumnLetterToNumber(string columnName)
{
if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
columnName = columnName.ToUpperInvariant();
int sum = 0;
for (int i = 0; i < columnName.Length; i++)
{
sum *= 26;
char letter = columnName[i];
sum += (letter - ('A' - 1));
}
sum--;
return sum;
}
// return the DataTable
public static DataTable FromXLSX(string filePath, string sheet, bool hasHeaders)
{
try
{
// Create the new datatable.
DataTable dtexcel = new DataTable();
// Define the SQL for querying the Excel spreadsheet.
HDR = hasHeaders ? "Yes" : "No"; // "HDR=Yes;" indicates that the first row contains column names, not data
string IMEX = "1";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath
+ ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=" + IMEX + ";\"";
// Create connection:
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
if (!sheet.EndsWith("_"))
{
// Query data from the sheet
string query = "SELECT * FROM [" + sheet + "$]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
// Fill the datatable:
daexcel.Fill(dtexcel);
}
// Close connection.
conn.Close();
// Set the datatable.
return dtexcel;
}
catch { throw; }
}
}
但是我注意到这个问题:从列A开始, DataTable从数据的第一列读取数据!是索引的噩梦。例如:
But I have noticed this issue: if datas don't start from the column 'A', the DataTable read data from the first column with data! Is a nightmare for the indexes. For example:
...在此情况下,列A被忽略(DataTable从B开头的数据),这样会使单元格代码(如A1,B5,AO11...)的使用无效,因为方法 ColumnLetterToNumber(string columnName)
被扭曲。
... in this case the column 'A' is ignored (the DataTable takes datas starting from 'B') and this invalidates the use of cell codes (like "A1", "B5", "AO11"...) because the method ColumnLetterToNumber(string columnName)
is distorted.
有人知道我如何强加DataTable从A柱?或使用单元代码从Excel获取数据的其他方式?
Someone knows how I can impose that the DataTable gets datas starting from the 'A' column? Or alternative ways to get data from Excel using cell codes?
推荐答案
您可以使用此查询:
string query = "SELECT NULL AS EmptyColumn, * FROM [" + sheet + "$]";
这篇关于在c#中读取Excel,其中一些列为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!