| public static PurgeTempApplicationDirectory ( ) : void | ||
| 리턴 | 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);
}
