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; }
}
}
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:
... 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.
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?
