private static void transform()
{
using (Pnyx p = new Pnyx())
{
p.read("C:/dev/asclepius/prod_import/BHC Patients from 1-1-15 thru 10-31-2018.csv");
p.parseCsv(hasHeader: true);
p.withColumns(p2 => p2.lineTransformer(new DateTransform
{
formatSource = DateUtil.FORMAT_MDYYYY, formatDestination = DateUtil.FORMAT_ISO_8601_DATE
}), 4, 5, 6);
p.rowTransformerFunc(row =>
{
String fullName = row[2];
Name name = NameUtil.parseFullName(fullName);
if (name == null)
{
return(null);
}
// Expands name into 4 columns
row = RowUtil.replaceColumn(row, 3, name.firstName, name.middleName, name.lastName, name.suffix);
return(row);
});
p.tee(p2 =>
{
p2.removeColumns(7 + 3, 8 + 3, 9 + 3); // plus 3 from name split above
p2.rowFilter(new RepeatFilter());
p2.write("C:/dev/asclepius/prod_import/bhc_discharges.csv");
});
p.widthColumns(9 + 3); // plus 3 from name split above
p.write("C:/dev/asclepius/prod_import/bhc_discharges_diagnosis.csv");
p.process();
}
}
private static void ccmNames()
{
using (Pnyx p = new Pnyx())
{
p.read("C:/dev/asclepius/prod_import/ccm_names.csv");
p.parseCsv(hasHeader: true);
p.rowTransformerFunc(row =>
{
String lastName = row[1];
Tuple <String, String> lastNameSuffix = NameUtil.parseSuffix(lastName);
if (lastNameSuffix.Item2 == null)
{
return(null);
}
// Expands name into 2 columns
row = RowUtil.replaceColumn(row, 2, lastNameSuffix.Item1, lastNameSuffix.Item2);
return(row);
});
p.rowTransformerFunc(row =>
{
for (int i = 0; i < row.Count; i++)
{
row[i] = TextUtil.encodeSqlValue(row[i]);
}
return(row);
});
p.print("update bhc_patient_ccm set lastname=$2, suffix=$3 where patientid=$1;");
p.write("C:/dev/asclepius/prod_import/ccm_names_update.sql");
p.process();
}
}
/// <summary>
/// Try to convert an object into a key value string
/// </summary>
/// <param name="o"></param>
/// <param name="keyString"></param>
/// <returns></returns>
public static bool TryGetKeyValueString(
object o,
out string keyString)
{
int intVal;
keyString = null;
if (o == null || o is DBNull)
{
return(false);
}
else if (o is string)
{
keyString = o as string;
return(true);
}
else if (RowUtil.TryConvertToInt(o, out intVal))
{
keyString = string.Format("{0,8:00000000}", intVal); // normalized for database CorpId
return(true);
}
else
{
keyString = o as string;
return(Lex.IsDefined(keyString));
}
}
public bool shouldKeepRow(List <String> row)
{
if (RowUtil.isEqual(row, previousRow))
{
return(false);
}
previousRow = row;
return(true);
}
/// <summary>
/// 将一个<see cref="DataRow"/>复制到一个<see cref="DataTable"/>中
/// </summary>
/// <param name="dataRow"></param>
/// <param name="dataTable"><see cref="DataTable"/></param>
public static void Copy(this DataRow dataRow, DataTable dataTable)
{
if (dataRow == null)
{
return;
}
if (dataTable == null)
{
dataTable = new DataTable();
}
RowUtil.AddColumn(dataTable, dataRow.Table.Columns.Cast <DataColumn>().ToArray());
DataRow templateRow = dataTable.NewRow();
templateRow.ItemArray = dataRow.ItemArray;
dataTable.Rows.Add(templateRow);
}
public void removeColumns(int original, String columnNumbersText, String expectedText)
{
List <String> source = new List <String>(original);
for (int i = 0; i < original; i++)
{
source.Add((i + 1).ToString());
}
HashSet <int> columnNumbers = new HashSet <int>(TextUtil.parseInts(columnNumbersText));
List <String> actual = RowUtil.removeColumns(source, columnNumbers);
String actualText = String.Join(",", actual);
Assert.Equal(expectedText, actualText);
}
private void verifyRows(String source, String[] rowA, String[] rowB, Action <CsvStreamToRowProcessor> callback = null)
{
List <String> actualA = null, actualB = null;
List <List <String> > rows = parseRows(source, callback);
if (rows.Count > 0)
{
actualA = rows[0];
}
if (rows.Count > 1)
{
actualB = rows[1];
}
Assert.True(RowUtil.isEqual(actualA, rowA.asRow()));
Assert.True(RowUtil.isEqual(actualB, rowB.asRow()));
}
/// <summary>
/// 将一个数据行复制到一个新的数据行
/// </summary>
/// <param name="fromDataRow">来源数据行</param>
/// <param name="toDataRow">要复制的数据行</param>
public static void CopyTo(this DataRow fromDataRow, DataRow toDataRow)
{
//得到来源数据表
DataTable fromTable = fromDataRow.Table;
//得到目标数据表
DataTable toTable = toDataRow.Table;
//遍历来源数据表的所有列
foreach (DataColumn column in fromTable.Columns)
{
//给目标数据表增加相应的列
toTable.AddColumn(column.ColumnName, column.DataType);
//取出刚刚增加的那一列
DataColumn toColumn = toTable.Columns[column.ColumnName];
//对目标数据行进行赋值
toDataRow[toColumn] = RowUtil.Get(fromDataRow, toColumn.DataType, toColumn.ColumnName);
}
}
/// <summary>
/// <inheritdoc cref="RowUtil.AddColumn(DataTable, string[])"/>
/// </summary>
/// <param name="dataTable"></param>
/// <param name="entityType">要添加到<see cref="DataTable"/>的对象的类型</param>
public static void AddColumn(this DataTable dataTable, Type entityType)
{
if (dataTable == null)
{
return;
}
foreach (PropertyInfo property in entityType.GetProperties())
{
RowUtil.AddColumn(dataTable, property.Name);
}
foreach (FieldInfo field in entityType.GetFields())
{
string columnName = field.Name;
if (dataTable.Columns.Contains(columnName))
{
int repeatCount = dataTable.Columns.Cast <DataColumn>().Count(p => p.ColumnName.ToLower().Equals(columnName));
columnName = string.Format("{0}_{1}", field.Name, repeatCount);
RowUtil.AddColumn(dataTable, columnName);
}
}
}
public void replaceColumns(int original, int columnNumber, String replacementText, String expectedText)
{
List <String> source = new List <String>(original);
for (int i = 0; i < original; i++)
{
source.Add((i + 1).ToString());
}
String[] replacements = replacementText.Split(',');
try
{
List <String> actual = RowUtil.replaceColumn(source, columnNumber, replacements);
String actualText = String.Join(",", actual);
Assert.Equal(expectedText, actualText);
}
catch (Exception)
{
Assert.Null(expectedText);
}
}
public static IEnumerable <DataSet> ReadExcelFile(string excelPath)
{
_excelConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0; HDR = YES; IMEX = 1\";", excelPath);
_excelConnFix = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0; HDR = YES; IMEX = 1\";", excelPath);
OleDbConnection conn = OpenConn(_excelConnFix);
if (conn == null)
{
throw new Exception("无法读取Excel文件.请检查Excel文件格式是否合法");
}
var sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "TABLE" });
List <DataSet> result = new List <DataSet>();
List <string> sheetCache = new List <string>();
foreach (DataRow sheet in sheets.Rows)
{
string tableName = RowUtil.GetString(sheet, "TABLE_NAME").Substring(0, RowUtil.GetString(sheet, "TABLE_NAME").IndexOf('$'));
if (sheetCache.Contains(tableName))
{
continue;
}
sheetCache.Add(tableName);
DataSet ds = new DataSet(tableName);
try
{
string excelSql = $"select * from [{tableName}$]";
OleDbDataAdapter da = new OleDbDataAdapter(excelSql, conn);
da.Fill(ds);
result.Add(ds);
}
catch (Exception ex)
{
throw ex;
}
}
return(result);
}
private void Start()
{
_rowUtil = RowUtil.Instance;
_uiController = UIController.Instance;
}
public async Task <IActionResult> UploadQuestionFile([FromForm] FileUpload fileUpload, [FromServices] IWebHostEnvironment enviroment)
{
if (ModelState.IsValid)
{
var filePath = Path.Combine(enviroment.WebRootPath, "Upload", loginUser.UserName, DateTime.Now.ToString("yyyyMMdd"));
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
var fileName = string.Format("{0}{1}", Guid.NewGuid(), Path.GetExtension(fileUpload.UploadFile.FileName));
fileName = Path.Combine(filePath, fileName);
using (FileStream fs = new FileStream(fileName, FileMode.Append, FileAccess.Write))
{
fileUpload.UploadFile.CopyTo(fs);
}
IEnumerable <DataSet> excelData = ReadQuestionFromExcel.ReadExcelFile(fileName);
//解析Excel数据
foreach (DataSet ds in excelData)
{
DataTable excelTable = ds.Tables[0];
if (excelTable == null || excelTable.Rows.Count < 2)
{
continue;
}
DataRow titleRow = excelTable.Rows[0]; //拿到标题行
int columnNum = excelTable.Columns.Count; //得到列数
int questionColumnIndex = 0, answerColumnIndex = 0;
List <int> solutionColumnIndexs = new List <int>();
//得到题目列，答案列，正确答案列的索引
for (int i = 0; i < columnNum; i++)
{
string rowValue = RowUtil.GetString(titleRow, excelTable.Columns[i].ColumnName);
if (rowValue.Equals("题目"))
{
questionColumnIndex = i;
}
if (rowValue.StartsWith("选项"))
{
if (!solutionColumnIndexs.Contains(i))
{
solutionColumnIndexs.Add(i);
}
}
if (rowValue.Equals("答案"))
{
answerColumnIndex = i;
}
}
DataColumn questionColumn = excelTable.Columns[questionColumnIndex];
DataColumn answerColumn = excelTable.Columns[answerColumnIndex];
DataColumn[] solutionColumns = new DataColumn[solutionColumnIndexs.Count];
//移除标题行
excelTable.Rows.Remove(titleRow);
for (int i = 0; i < solutionColumnIndexs.Count; i++)
{
solutionColumns[i] = excelTable.Columns[solutionColumnIndexs[i]];
}
//生成问题和答案
for (int i = 0; i < excelTable.Rows.Count; i++)
{
DataRow currentRow = excelTable.Rows[i];
//得到题目
string questionName = RowUtil.GetString(currentRow, questionColumn.ColumnName);
if (string.IsNullOrEmpty(questionName))
{
continue;
}
//得到正确答案序号
string answer = RowUtil.GetString(currentRow, answerColumn.ColumnName);
List <string> solutions = new List <string>();
//得到所有答案
foreach (DataColumn column in solutionColumns)
{
string solution = RowUtil.GetString(currentRow, column.ColumnName);
solutions.Add(solution);
}
CreateQuestionInfo questionInfo = new CreateQuestionInfo();
questionInfo.Name = questionName;
questionInfo.QuestionType = QuestionType.Radio;
questionInfo.QuestionTypeInt = (int)questionInfo.QuestionType;
questionInfo.QuestionScore = 1;
questionInfo.QuestionGroupName = fileUpload.UploadFile.FileName.Replace(Path.GetExtension(fileUpload.UploadFile.FileName), null);
questionInfo.SolutionNames = solutions.AsEnumerable();
List <double> solutionScore = new List <double>();
string regexStr = @"^[A-Za-z]+";
//计算选项得分
foreach (string item in questionInfo.SolutionNames)
{
//如果正确答案是A~Z，就判断所有选项的开头，否则判断答案是否包含
if (RegularUtil.Regex(regexStr, answer))
{
if (item.StartsWith(answer)) //选项的开头和答案一致，证明是正确答案
{
solutionScore.Add(1);
}
else
{
solutionScore.Add(0);
}
}
else
{
if (item.Contains(answer)) //选项包含答案，就认为是正确答案
{
solutionScore.Add(1);
}
else
{
solutionScore.Add(0);
}
}
}
questionInfo.SolutionScore = solutionScore.AsEnumerable();
//去掉选项前边的序号
List <string> solutons1 = new List <string>();
foreach (string solution in solutions)
{
int index = solution.IndexOf('.');
string solutionValue = solution.Substring(index + 1, solution.Length - index - 1);
solutons1.Add(solutionValue);
}
questionInfo.SolutionNames = solutons1.AsEnumerable();
CreateQuestion(questionInfo);
}
}
return(RedirectToAction(nameof(Index)));
}
ModelState.AddModelError("UploadFile", "目前只支持Excel文件(.xls/.xlsx)哦");
return(View(fileUpload));
}
public List <String> transformHeader(List <String> header)
{
header = RowUtil.fixWidth(header, columns, "");
RowUtil.setDefaultHeaderNames(header);
return(header);
}
public List <String> transformRow(List <String> row)
{
return(RowUtil.fixWidth(row, columns, pad));
}
public List <String> transformRow(List <String> row)
{
return(RowUtil.insertBlankColumns(row, columnNumbers, pad));
}
private void Start()
{
_rowUtil = RowUtil.Instance;
}
public List <String> transformHeader(List <String> header)
{
return(RowUtil.duplicateColumns(header, columnNumbers));
}
/// <summary>
/// <inheritdoc cref="RowUtil.AddColumn(DataTable, string[])"/>
/// </summary>
/// <param name="dataTable"></param>
/// <param name="columns">要添加的列</param>
public static void AddColumn(this DataTable dataTable, params string[] columns)
{
RowUtil.AddColumn(dataTable, columns);
}
private void Awake()
{
Instance = this;
}
public List <String> transformHeader(List <String> header)
{
header = RowUtil.insertBlankColumns(header, columnNumbers, "");
return(RowUtil.setDefaultHeaderNames(header));
}
public List <String> transformRow(List <String> row)
{
return(RowUtil.duplicateColumns(row, columnNumbers));
}
public static int main()
{
using (Pnyx p = new Pnyx())
{
p.setSettings(outputNewline: StreamInformation.newlineString(NewLineEnum.Unix));
p.read(@"c:/dev/asclepius/prod_import/American Academy of Private Physicians.csv");
p.parseCsv(hasHeader: true);
p.hasColumns(true, 2);
p.withColumns(p2 => { p2.lineTransformerFunc(line => line == "" ? "-66" : line); }, 1, 2, 3, 4, 5);
p.rowTransformerFunc(row =>
{
row[3] = TextUtil.extractAlpha(row[3]); // removes periods from title
return(row);
});
p.rowTransformerFunc(row =>
{
row[7] = ZipCodeUtil.parseZipCode(row[7], true);
return(row);
});
p.rowTransformerFunc(row =>
{
row[8] = PhoneUtil.parsePhone(row[8]);
return(row);
});
p.rowTransformerFunc(row =>
{
row[9] = EmailUtil.validateAndRepair(row[9]);
return(row);
});
p.rowTransformerFunc(row =>
{
String firstName = row[1];
String lastName = row[2];
firstName = firstName.Replace(",", " ");
lastName = lastName.Replace(",", " ");
String wholeName = firstName + " " + lastName;
Name name = NameUtil.parseFullName(wholeName);
row[1] = name.firstName;
row[2] = name.lastName;
row = RowUtil.insertColumns(row, 4, name.suffix);
row = RowUtil.insertColumns(row, 3, name.middleName);
return(row);
});
p.widthColumns(13);
p.headerNames("Credentials", "FirstName", "MiddleName", "LastName", "Suffix", "Title", "StreetAddress", "City", "State", "ZipCode", "Phone", "Email", "CompanyName");
p.write(@"c:/dev/asclepius/prod_import/aapp.csv");
}
using (Pnyx p = new Pnyx())
{
p.read(@"c:/dev/asclepius/prod_import/aapp.csv");
p.parseCsv();
p.columnDefinition(hasHeaderRow: true, maxWidth: true, nullable: true);
p.swapColumnsAndRows();
p.writeStdout();
}
return(0);
}