public static PurgeTempApplicationDirectory ( ) : void | ||
return | void |
static public DataTable LoadDataTableFromCSV(string csvPath, char?separator, Encoding encoding) { DataTable result = null; bool isHeader = true; Regex regexp = null; string[] lines = null; try { lines = File.ReadAllLines(csvPath, encoding); } catch { //Try by copying the file... string newPath = FileHelper.GetTempUniqueFileName(csvPath); File.Copy(csvPath, newPath); lines = File.ReadAllLines(newPath, encoding); FileHelper.PurgeTempApplicationDirectory(); } foreach (string line in lines) { var line2 = line.Trim(); if (string.IsNullOrWhiteSpace(line2)) { continue; } if (regexp == null) { if (separator == null) { //use the first line to determine the separator between , and ; separator = ','; if (line2.Split(';').Length > line2.Split(',').Length) { separator = ';'; } } var sep2 = (separator.Value == '|' || separator.Value == ':' ? Path.DirectorySeparatorChar.ToString() : "") + separator.Value; string exp = "(?<=^|" + sep2 + ")(\"(?:[^\"]|\"\")*\"|[^" + sep2 + "]*)"; regexp = new Regex(exp); } MatchCollection collection = regexp.Matches(line2); if (isHeader) { result = new DataTable(); for (int i = 0; i < collection.Count; i++) { result.Columns.Add(new DataColumn(ExcelHelper.FromCsv(collection[i].Value), typeof(string))); } isHeader = false; } else { var row = result.Rows.Add(); for (int i = 0; i < collection.Count && i < result.Columns.Count; i++) { row[i] = ExcelHelper.FromCsv(collection[i].Value); if (row[i].ToString().Contains("\0")) { row[i] = ""; } } } } return(result); }
/// <summary> /// Load a DataTable from an Excel tab into the database. A start row, and/or colum can be specified. An end column can be specified. /// </summary> static public DataTable LoadDataTableFromExcel(string excelPath, string tabName = "", int startRow = 1, int startCol = 1, int endColIndex = 0) { bool hasColNames = (endColIndex == 0); ExcelPackage package; try { package = new ExcelPackage(new FileInfo(excelPath)); } catch { string newPath = FileHelper.GetTempUniqueFileName(excelPath); FileHelper.PurgeTempApplicationDirectory(); File.Copy(excelPath, newPath, true); package = new ExcelPackage(new FileInfo(newPath)); } var workbook = package.Workbook; ExcelWorksheet worksheet = null; if (!string.IsNullOrEmpty(tabName)) { foreach (ExcelWorksheet ws in workbook.Worksheets) { if (ws.Name.ToLower() == tabName.ToLower()) { worksheet = ws; break; } } if (workbook.Worksheets.Count == 0) { throw new Exception("No sheet in the workbook."); } if (worksheet == null) { throw new Exception("Unable to find tab name specified."); } } else { worksheet = workbook.Worksheets[0]; } DataTable result = new DataTable(); int colTitle = startCol; int colCount = 0; while ((hasColNames && (worksheet.Cells[startRow, colCount + 1].Value != null)) || colCount < endColIndex) { colCount++; } while ((hasColNames && (worksheet.Cells[startRow, colTitle].Value != null)) || colTitle <= endColIndex) { int rowTitle = startRow; string colName = worksheet.Cells[startRow, colTitle].Address; if (hasColNames) { colName = worksheet.Cells[startRow, colTitle].Text; if (string.IsNullOrEmpty(colName)) { colName = worksheet.Cells[startRow, colTitle].Value.ToString(); } rowTitle++; } else { colName = colName.Replace("'", "").Replace("!", "").Replace(worksheet.Name, ""); } //get the type Type t = typeof(string); if (worksheet.Cells[rowTitle, colTitle] != null && worksheet.Cells[rowTitle, colTitle].Value != null) { t = worksheet.Cells[rowTitle, colTitle].Value.GetType(); //check that the type is consistent if (t != typeof(string)) { rowTitle++; while (!IsRowEmpty(worksheet, rowTitle, startCol, colCount)) { if (worksheet.Cells[rowTitle, colTitle].Value != null && t != worksheet.Cells[rowTitle, colTitle].Value.GetType()) { t = typeof(string); break; } rowTitle++; } } } result.Columns.Add(colName, t); colTitle++; } //copy values int rowValue = startRow; if (hasColNames) { rowValue++; } while (!IsRowEmpty(worksheet, rowValue, startCol, result.Columns.Count)) { DataRow dr = result.Rows.Add(); for (int colValue = startCol; colValue < startCol + result.Columns.Count; colValue++) { object val = null; if (worksheet.Cells[rowValue, colValue].Value != null) { string valText = worksheet.Cells[rowValue, colValue].Text; if (!string.IsNullOrEmpty(worksheet.Cells[rowValue, colValue].Text)) { valText = worksheet.Cells[rowValue, colValue].Value.ToString(); } if (string.IsNullOrEmpty(valText)) { val = worksheet.Cells[rowValue, colValue].Value; } else { val = valText; } } if (val == null) { val = DBNull.Value; } dr[colValue - startCol] = val; } rowValue++; } return(result); }
public DataTable LoadDataTableFromCSV(string csvPath, char?separator = null) { if (MyLoadDataTableFromCSV != null) { return(MyLoadDataTableFromCSV(csvPath, separator)); } DataTable result = null; bool isHeader = true; Regex regexp = null; string[] lines = null; try { lines = File.ReadAllLines(csvPath, DefaultEncoding); } catch { //Try by copying the file... string newPath = FileHelper.GetTempUniqueFileName(csvPath); File.Copy(csvPath, newPath); lines = File.ReadAllLines(newPath, DefaultEncoding); FileHelper.PurgeTempApplicationDirectory(); } foreach (string line in lines) { if (string.IsNullOrWhiteSpace(line)) { continue; } if (regexp == null) { string exp = "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)"; if (separator == null) { //use the first line to determine the separator between , and ; separator = ','; if (line.Split(';').Length > line.Split(',').Length) { separator = ';'; } } if (separator != ',') { exp = exp.Replace(',', separator.Value); } regexp = new Regex(exp); } MatchCollection collection = regexp.Matches(line); if (isHeader) { result = new DataTable(); for (int i = 0; i < collection.Count; i++) { result.Columns.Add(new DataColumn(ExcelHelper.FromCsv(collection[i].Value), typeof(string))); } isHeader = false; } else { var row = result.Rows.Add(); for (int i = 0; i < collection.Count && i < result.Columns.Count; i++) { row[i] = ExcelHelper.FromCsv(collection[i].Value); } } } return(result); }