public static ColumnInfo[] GetColumnInfoUsed(string sFileName, string sSheetName)
{
if (!string.IsNullOrEmpty(sSheetName) && (!string.IsNullOrEmpty(sFileName)))
{
using (var excel = new ExcelReaderWriter(sFileName, sSheetName))
{
excel.OpenWorkbook(true);
int nColumn = excel.ActiveWorksheet.UsedRange.Columns.Count;
int?nColumnTo = GetLastCoumnIndex(excel.ActiveWorksheet.UsedRange);
if (!nColumnTo.HasValue)
{
excel.CloseWorkbook();
return(null);
}
int nColumnFrom = nColumnTo.Value - nColumn + 1;
List <ColumnInfo> list = new List <ColumnInfo>();
for (int i = nColumnFrom; i <= nColumnTo; i++)
{
list.Add(new ColumnInfo()
{
Index = i,
Name = GetNameFromColumnIndex(i)
});
}
excel.CloseWorkbook();
return(list.ToArray());
}
}
return(null);
}
/// <summary>
/// Get the list of Columns for sheet
/// </summary>
/// <param name="fileName"></param>
/// <param name="aReadOnly"></param>
/// <returns></returns>
public static void SetColumnInfos(string fileName, SheetInfo sheetInfo)
{
if (sheetInfo.Columns != null && sheetInfo.Columns.Count > 0)
{
return;
}
if (!File.Exists(fileName))
{
return;
}
IList <SheetInfo> m_SheetInfos = new List <SheetInfo>();
ExcelReaderWriter m_ExcelReaderWriter = new ExcelReaderWriter(fileName, 1);
try
{
m_ExcelReaderWriter.OpenWorkbook(true);
foreach (Excel.Worksheet m_WorkSheet in m_ExcelReaderWriter.ActiveWorkbook.Sheets)
{
if (m_WorkSheet.Name == sheetInfo.Name)
{
#region Set Column Infos
object[,] m_Values = (object[, ])m_WorkSheet.UsedRange.Value2;
if (m_Values != null)
{
int m_UseRangeRowLength = (int)m_Values.GetLongLength(0);
int m_UseRangeColumnLength = (int)m_Values.GetLongLength(1);
//Recheck
// - BeginHeaderColumnIndex
if (sheetInfo.BeginHeaderColumnIndex == 0)
{
sheetInfo.BeginHeaderColumnIndex = 1;
}
else if (sheetInfo.BeginHeaderColumnIndex > m_UseRangeColumnLength)
{
sheetInfo.BeginHeaderColumnIndex = m_UseRangeColumnLength;
}
// - EndHeaderColumnIndex
if (sheetInfo.EndHeaderColumnIndex == 0)
{
sheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength;
}
else if (sheetInfo.EndHeaderColumnIndex < sheetInfo.BeginHeaderColumnIndex)
{
sheetInfo.EndHeaderColumnIndex = sheetInfo.BeginHeaderColumnIndex;
}
else if (sheetInfo.EndHeaderColumnIndex > m_UseRangeColumnLength)
{
sheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength;
}
// - BeginRowIndex
if (sheetInfo.BeginRowIndex == 0)
{
sheetInfo.BeginRowIndex = 1;
}
else if (sheetInfo.BeginRowIndex > m_UseRangeRowLength)
{
sheetInfo.BeginRowIndex = m_UseRangeRowLength;
}
// - EndRowIndex
if (sheetInfo.EndRowIndex == 0)
{
sheetInfo.EndRowIndex = m_UseRangeRowLength;
}
else if (sheetInfo.EndRowIndex > m_UseRangeRowLength)
{
sheetInfo.EndRowIndex = m_UseRangeRowLength;
}
else if (sheetInfo.EndRowIndex < sheetInfo.BeginRowIndex)
{
sheetInfo.EndRowIndex = sheetInfo.BeginRowIndex;
}
// - HeaderRowIndex
if (sheetInfo.HeaderRowIndex == 0)
{
sheetInfo.HeaderRowIndex = 1;
}
else if (sheetInfo.HeaderRowIndex > sheetInfo.EndRowIndex)
{
sheetInfo.HeaderRowIndex = sheetInfo.EndRowIndex;
}
for (int c = sheetInfo.BeginHeaderColumnIndex; c <= sheetInfo.EndHeaderColumnIndex; c++)
{
ColumnInfo m_ColumnInfo = new ColumnInfo()
{
Index = c,
Header = ConvertedColumnData.Instance.Data[c],
Name = ConvertedColumnData.Instance.Data[c]
};
sheetInfo.Columns.Add(m_ColumnInfo);
}
}
#endregion
return;
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
finally
{
m_ExcelReaderWriter.CloseWorkbook();
}
}
/// <summary>
/// Get the list of Sheets
/// </summary>
/// <param name="fileName"></param>
/// <param name="aReadOnly"></param>
/// <returns></returns>
public static IList <SheetInfo> GetSheetInfos(string fileName, bool aReadOnly)
{
if (!File.Exists(fileName))
{
return(null);
}
IList <SheetInfo> m_SheetInfos = new List <SheetInfo>();
ExcelReaderWriter m_ExcelReaderWriter = new ExcelReaderWriter(fileName, 1);
try
{
m_ExcelReaderWriter.OpenWorkbook(true);
int i = 1;
foreach (Excel.Worksheet m_WorkSheet in m_ExcelReaderWriter.ActiveWorkbook.Sheets)
{
var m_SheetInfo = new SheetInfo()
{
Name = m_WorkSheet.Name, Index = i
};
//#region Set Column Infos
//object[,] m_Values = (object[,])m_ExcelReaderWriter.ActiveWorksheet.UsedRange.Value2;
//if (m_Values != null)
//{
// int m_UseRangeRowLength = (int)m_Values.GetLongLength(0);
// int m_UseRangeColumnLength = (int)m_Values.GetLongLength(1);
// //Recheck
// // - BeginHeaderColumnIndex
// if (m_SheetInfo.BeginHeaderColumnIndex == 0)
// m_SheetInfo.BeginHeaderColumnIndex = 1;
// else if (m_SheetInfo.BeginHeaderColumnIndex > m_UseRangeColumnLength)
// m_SheetInfo.BeginHeaderColumnIndex = m_UseRangeColumnLength;
// // - EndHeaderColumnIndex
// if (m_SheetInfo.EndHeaderColumnIndex == 0)
// m_SheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength;
// else if (m_SheetInfo.EndHeaderColumnIndex < m_SheetInfo.BeginHeaderColumnIndex)
// m_SheetInfo.EndHeaderColumnIndex = m_SheetInfo.BeginHeaderColumnIndex;
// else if (m_SheetInfo.EndHeaderColumnIndex > m_UseRangeColumnLength)
// m_SheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength;
// // - BeginRowIndex
// if (m_SheetInfo.BeginRowIndex == 0)
// m_SheetInfo.BeginRowIndex = 1;
// else if (m_SheetInfo.BeginRowIndex > m_UseRangeRowLength)
// m_SheetInfo.BeginRowIndex = m_UseRangeRowLength;
// // - EndRowIndex
// if (m_SheetInfo.EndRowIndex == 0)
// m_SheetInfo.EndRowIndex = m_UseRangeRowLength;
// else if (m_SheetInfo.EndRowIndex > m_UseRangeRowLength)
// m_SheetInfo.EndRowIndex = m_UseRangeRowLength;
// else if (m_SheetInfo.EndRowIndex < m_SheetInfo.BeginRowIndex)
// m_SheetInfo.EndRowIndex = m_SheetInfo.BeginRowIndex;
// // - HeaderRowIndex
// if (m_SheetInfo.HeaderRowIndex == 0)
// m_SheetInfo.HeaderRowIndex = 1;
// else if (m_SheetInfo.HeaderRowIndex > m_SheetInfo.EndRowIndex)
// m_SheetInfo.HeaderRowIndex = m_SheetInfo.EndRowIndex;
// for (int c = m_SheetInfo.BeginHeaderColumnIndex; c <= m_SheetInfo.EndHeaderColumnIndex; c++)
// {
// ColumnInfo m_ColumnInfo = new ColumnInfo()
// {
// Index = c,
// Header = ConvertedColumnData.Instance.Data[c],
// Name = ConvertedColumnData.Instance.Data[c]
// };
// m_SheetInfo.Columns.Add(m_ColumnInfo);
// }
//}
//#endregion
m_SheetInfos.Add(m_SheetInfo);
i++;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
m_SheetInfos.Clear();
}
finally
{
m_ExcelReaderWriter.CloseWorkbook();
}
return(m_SheetInfos);
}