private IWorksheet GetWorksheet()
{
if (null == _worksheet)
{
var www = new WebClient();
var buf = www.DownloadData(_url);
// test for xml or pkzip (ie. packed xlsx format) leadin
// also account for leading text encoding markers in xml
if (buf.Length <= 10)
{
return(null);
}
if ((buf[0] == 'P' && buf[1] == 'K') ||
(buf[0] == '<' && buf[1] == '?' && buf[2] == 'x') ||
(buf[1] == '<' && buf[2] == '?' && buf[3] == 'x') ||
(buf[2] == '<' && buf[3] == '?' && buf[4] == 'x') ||
(buf[3] == '<' && buf[4] == '?' && buf[5] == 'x') ||
(buf[4] == '<' && buf[5] == '?' && buf[6] == 'x'))
{
using (var ms = new MemoryStream(buf))
_workbook = WorkbookFactory.GetExcel2007Reader(ms);
}
else
{
using (var ms = new MemoryStream(buf))
_workbook = WorkbookFactory.GetExcelBIFFReader(ms);
}
_worksheet = _workbook.Worksheets.GetWorksheetByIndex(0);
}
return(_worksheet);
}
public void KoograTest()
{
var fileName = TestHelper.TestsheetPath("TestSpreadsheet1.xlsx");
var reader = WorkbookFactory.GetExcel2007Reader(fileName);
var worksheet = reader.Worksheets.GetWorksheetByIndex(0);
var list = new List <object>();
for (var r = worksheet.FirstRow; r <= worksheet.LastRow; ++r)
{
var row = worksheet.Rows.GetRow(r);
if (row != null)
{
for (var colCount = worksheet.FirstCol; colCount <= worksheet.LastCol; ++colCount)
{
var cellData = string.Empty;
if (row.GetCell(colCount) != null && row.GetCell(colCount).Value != null)
{
cellData = row.GetCell(colCount).Value.ToString();
}
list.Add(cellData);
}
}
}
if (list.Count != 1540)
{
throw new Exception($"{list.Count}");
}
}
/// <summary>
/// Перевод листа xlsx в DataTable со сложным форматом
/// </summary>
/// <param name="pathXlsx">Путь к xlsx</param>
/// <param name="sheetName">Имя листа</param>
/// <param name="indexColumn">Индекс колонки по умолчанию 1</param>
/// <param name="indexRow">Индекс строки с какой начинать</param>
/// <returns></returns>
public DataTable GetDateTableXslxFormatNodDouble(string pathXlsx, string sheetName, int indexColumn = 1, uint indexRow = 0)
{
DataTable dt = new DataTable();
var xlFile = WorkbookFactory.GetExcel2007Reader(pathXlsx);
var sheet = xlFile.Worksheets.GetWorksheetByName(sheetName, true);
uint minRow = sheet.FirstRow + indexRow;
uint maxRow = sheet.LastRow;
IRow firstRow = sheet.Rows.GetRow(minRow);
uint minCol = sheet.FirstCol;
uint maxCol = sheet.LastCol;
for (uint i = minCol; i <= maxCol; i++)
{
var valueNameColums = firstRow.GetCell(i).GetFormattedValue();
if (!dt.Columns.Contains(valueNameColums))
{
dt.Columns.Add(valueNameColums);
}
else
{
dt.Columns.Add(string.Concat(valueNameColums, indexColumn));
indexColumn++;
}
}
for (uint i = minRow + 1; i <= maxRow; i++)
{
IRow row = sheet.Rows.GetRow(i);
if (row != null)
{
DataRow dr = dt.NewRow();
for (uint j = minCol; j <= maxCol; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.Value != null)
{
dr[Convert.ToInt32(j)] = cell.Value != null?cell.GetFormattedValue() : string.Empty;
}
else
{
dr[Convert.ToInt32(j)] = null;
}
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}
public void AsDataSetXlsx()
{
IWorkbook workbook = WorkbookFactory.GetExcel2007Reader(@"..\..\Files\AsDataSet\AsDataSet.xlsx");
DataSet set = workbook.AsDataSet(true);
Assert.AreEqual(1, set.Tables.Count);
Assert.AreEqual("Sheet1", set.Tables[0].TableName);
Assert.AreEqual(3, set.Tables[0].Rows.Count);
Assert.AreEqual(6, set.Tables[0].Columns.Count);
string[] expectedCols = { "A", "B", "C", "E", "F", "G" };
for (int i = 0; i < set.Tables[0].Columns.Count; i++)
{
Assert.AreEqual(expectedCols[i], set.Tables[0].Columns[i].ColumnName);
}
}
Dictionary <string, uint> GetFileColumns(String path)
{
Dictionary <string, uint> cols = new Dictionary <string, uint>();
IWorkbook genericWB = WorkbookFactory.GetExcel2007Reader(path);
IWorksheet genericWS = genericWB.Worksheets.GetWorksheetByIndex(0);
uint r = genericWS.FirstRow;
IRow row = genericWS.Rows.GetRow(r);
for (uint c = genericWS.FirstCol; c <= genericWS.LastCol; ++c)
{
String cell = row.GetCell(c).Value.ToString();
if (cell != null)
{
cols[cell] = c;
}
}
return(cols);
}
public void GetFileFields(String file)
{
String path = Server.MapPath(file);
List <String> list = new List <String>();
IWorkbook genericWB = WorkbookFactory.GetExcel2007Reader(path);
IWorksheet genericWS = genericWB.Worksheets.GetWorksheetByIndex(0);
uint r = genericWS.FirstRow;
IRow row = genericWS.Rows.GetRow(r);
for (uint c = genericWS.FirstCol; c <= genericWS.LastCol; ++c)
{
Object cell = row.GetCell(c).Value;
if (cell != null)
{
list.Add(cell.ToString());
}
}
String[] items = list.ToArray();
var json = new JavaScriptSerializer().Serialize(items);
Response.Write(json);
}
public void ImportExcelTrawl()
{
String path = Server.MapPath(Request["file"]);
if (File.Exists(path) == false)
{
die("File not found: " + path);
}
fileColums = GetFileColumns(path);
mappedColumns = new Dictionary <string, uint>();
// resolve indicators
Dictionary <uint, int> findicators = new Dictionary <uint, int>();
if (Request["indicators"] != null && Request["indicators"] != "")
{
String[] sindicators = Request["indicators"].Split('~');
foreach (String indicator in sindicators)
{
String[] indic = indicator.Split('|');
int indicatorID = int.Parse(indic[0]);
uint column = fileColums[indic[1]];
findicators[column] = indicatorID;
}
}
IWorkbook genericWB = WorkbookFactory.GetExcel2007Reader(path);
IWorksheet genericWS = genericWB.Worksheets.GetWorksheetByIndex(0);
for (uint r = genericWS.FirstRow + 1; r <= genericWS.LastRow; r++)
{
IRow row = genericWS.Rows.GetRow(r);
String survey = GetCellValue(row, "fieldSurvey");
String cruise = GetCellValue(row, "fieldCruise");
String trawl = GetCellValue(row, "fieldTrawl");
String Station = GetCellValue(row, "fieldStation");
String sLatHour = GetCellValue(row, "sLatHour");
String sLatMin = GetCellValue(row, "sLatMin");
String sLonHour = GetCellValue(row, "sLonHour");
String sLonMin = GetCellValue(row, "sLonMin");
String eLatHour = GetCellValue(row, "eLatHour");
String eLatMin = GetCellValue(row, "eLatMin");
String eLonHour = GetCellValue(row, "eLonHour");
String eLonMin = GetCellValue(row, "eLonMin");
String comments = GetCellValue(row, "Comments");
int dateYear = int.Parse(GetCellValue(row, "dateYear"));
int dateMonth = int.Parse(GetCellValue(row, "dateMonth"));
int dateDay = int.Parse(GetCellValue(row, "dateDay"));
double duration = double.Parse(GetCellValue(row, "Duration"));
Dictionary <int, string> indicators = new Dictionary <int, string>();
foreach (uint col in findicators.Keys)
{
int indicator = findicators[col];
indicators[indicator] = GetCellValue(row, col);
Response.Write("Row = " + r + ", Indicator: " + indicator + " = " + indicators[indicator] + "<br>");
}
double lat1 = -ParseCoord(sLatHour, sLatMin);
double lon1 = ParseCoord(sLonHour, sLonMin);
double lat2 = -ParseCoord(eLatHour, eLatMin);
double lon2 = ParseCoord(eLonHour, eLonMin);
manSurveys sv = new manSurveys();
int nSurveyID = sv.GetSurveyID(2, survey);
if (nSurveyID < 1)
{
die("Failed to create survey");
}
manStations st = new manStations();
int nStationID = st.GetStationID(Station, lat1, lon1);
if (nStationID < 1)
{
die("Failed to create station");
}
manEvents ev = new manEvents();
ev.res = Response;
int nEventID = ev.GetEventID(nSurveyID, nStationID, dateYear, dateMonth, dateDay, cruise, trawl, lat1, lon1, lat2, lon2, duration, comments, indicators);
}
Response.Write("cools!");
}
public void ImportExcelCatch()
{
String path = Server.MapPath(Request["file"]);
if (File.Exists(path) == false)
{
die("File not found: " + path);
}
fileColums = GetFileColumns(path);
mappedColumns = new Dictionary <string, uint>();
IWorkbook genericWB = WorkbookFactory.GetExcel2007Reader(path);
IWorksheet genericWS = genericWB.Worksheets.GetWorksheetByIndex(0);
for (uint r = genericWS.FirstRow + 1; r <= genericWS.LastRow; r++)
{
IRow row = genericWS.Rows.GetRow(r);
String Cruise = GetCellValue(row, "catchCruise");
String Trawl = GetCellValue(row, "catchTrawl");
String Station = GetCellValue(row, "catchStation");
String Common = GetCellValue(row, "catchCommon");
String Genus = GetCellValue(row, "catchGenus");
String Species = GetCellValue(row, "catchSpecies");
String Abundance = GetCellValue(row, "catchAbundance");
String Biomass = GetCellValue(row, "catchBiomass");
String Description = GetCellValue(row, "catchDescription");
if (Abundance.Trim() == "")
{
Abundance = "0";
}
if (Biomass.Trim() == "")
{
Biomass = "0";
}
Response.Write(Common + " " + Genus + " " + Species + ": ");
manSpecies ms = new manSpecies();
int species = ms.GetSpeciesID(Genus, Species, Common);
if (species == 0)
{
Response.Write("Failed to add species");
}
else
{
manEvents me = new manEvents();
int eventID = me.FindEventID(Cruise, Trawl, Station);
if (eventID == 0)
{
die(String.Format("Event not found: {0}, {1}, {2}", Cruise, Trawl, Station));
}
manEventSpecies mes = new manEventSpecies();
mes.addEventSpecies(eventID, species, double.Parse(Abundance), double.Parse(Biomass), Description);
}
Response.Write("<br>");
Response.Flush();
}
}
/// <summary>
/// Перевод листа xlsx в DataTable
/// </summary>
/// <param name="pathXlsx">Путь к xlsx</param>
/// <param name="sheetName">Имя листа</param>
/// <param name="indexColumn">Индекс колонки по умолчанию 1</param>
/// <param name="indexRow">Индекс строки с какой начинать</param>
/// <returns></returns>
public DataTable GetDateTableXslx(string pathXlsx, string sheetName, int indexColumn = 1, uint indexRow = 0)
{
DataTable dt = new DataTable();
Regex regex = new Regex(@"^\d+$");
var xlFile = WorkbookFactory.GetExcel2007Reader(pathXlsx);
var sheet = xlFile.Worksheets.GetWorksheetByName(sheetName, true);
uint minRow = sheet.FirstRow + indexRow;
uint maxRow = sheet.LastRow;
IRow firstRow = sheet.Rows.GetRow(minRow);
uint minCol = sheet.FirstCol;
uint maxCol = sheet.LastCol;
for (uint i = minCol; i <= maxCol; i++)
{
var valueNameColums = firstRow.GetCell(i).GetFormattedValue();
if (!dt.Columns.Contains(valueNameColums))
{
dt.Columns.Add(valueNameColums);
}
else
{
dt.Columns.Add(string.Concat(valueNameColums, indexColumn));
indexColumn++;
}
}
for (uint i = minRow + 1; i <= maxRow; i++)
{
IRow row = sheet.Rows.GetRow(i);
if (row != null)
{
DataRow dr = dt.NewRow();
for (uint j = minCol; j <= maxCol; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.Value != null)
{
double result;
// Try parsing in the current culture
if (!double.TryParse(cell.Value.ToString(), NumberStyles.Float, CultureInfo.CurrentCulture, out result) &&
// Then try in US english
!double.TryParse(cell.Value.ToString(), NumberStyles.Float,
CultureInfo.GetCultureInfo("en-US"), out result) &&
// Then in neutral language
!double.TryParse(cell.Value.ToString(), NumberStyles.Float,
CultureInfo.InvariantCulture, out result))
{
dr[Convert.ToInt32(j)] = cell.Value != null?cell.GetFormattedValue() : string.Empty;
}
else
{
if (cell.Value.ToString().Length == 20 && regex.IsMatch(cell.Value.ToString()))
{
dr[Convert.ToInt32(j)] = cell.Value != null?cell.GetFormattedValue() : string.Empty;
}
else
{
dr[Convert.ToInt32(j)] = result.ToString();
}
}
}
else
{
dr[Convert.ToInt32(j)] = null;
}
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}