public XLSXPlugin(string contentFormat, string contentEncoding, string currentFile, string currentWorkingDirectory)
{
this.contentFormat = new ContentType(contentFormat, contentEncoding);
this.currentFile = currentFile;
fileInput = new FileStream(Path.Combine(currentWorkingDirectory, this.currentFile), FileMode.Open, FileAccess.Read);
workBook = new XSSFWorkbook(this.fileInput);
currentPageName = currentFile.Substring(this.currentFile.LastIndexOf("/") + 1, this.currentFile.LastIndexOf("."));
this.currentWorkingDirectory = currentWorkingDirectory;
sheet = workBook.GetSheet("Sheet1");
cmsBlockFactory = new CMSBlockFactory(currentPageName);
processDataRows();
}
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)5);
sheet.SetColumnGroupCollapsed(4, true);
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 4; i <= 5; i++)
{
Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
cols = sheet.GetCTWorksheet().GetColsArray(0);
foreach (CT_Col col in cols.GetColArray())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
public void TestBug56644CreateBlank()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56644.xlsx");
try
{
wb.MissingCellPolicy = (MissingCellPolicy.CREATE_NULL_AS_BLANK);
ISheet sheet = wb.GetSheet("samplelist");
IRow row = sheet.GetRow(20);
row.CreateCell(2);
}
finally
{
wb.Close();
}
}
public static void ReadYinTaiStoreExcel()
{
Stream excel=new FileStream(@"E:\工作资料\门店帐号信息完整版.xlsx",FileMode.Open);
IWorkbook workbook = new XSSFWorkbook(excel);
ISheet sheet = workbook.GetSheet("门店信息");
var sb = new StringBuilder();
for (int i = sheet.FirstRowNum+1; i < sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
int j = row.FirstCellNum+3;
sb.AppendFormat(
"INSERT INTO [YintaiEvent].[dbo].[Store]([StoreId],[City],[StoreName],[CompanyFullName],[Address],[OfficeAddress],[Phone],[Bank1],[BankAccount1],[Bank2],[BankAccount2],[InUserId],[EditUserId],[InDate],[EditDate])VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',{13},{14})", i, row.GetCell(j-2),row.GetCell(j-1), row.GetCell(j), row.GetCell(j + 1), row.GetCell(j + 2), row.GetCell(j + 3), row.GetCell(j + 4), row.GetCell(j + 5), row.GetCell(j + 6), row.GetCell(j + 7), "0", "0", "GetDate()", "GetDate()");
sb.Append("\n");
}
var str = sb.ToString();
}
// Use this for initialization
void Start ()
{
Debug.Log(Application.dataPath + "/Resources/Data/Test.xlsx");
if(File.Exists(Application.dataPath + "/Resources/Data/Test.xlsx"))
{
Debug.Log("OK");
}
else
{
Debug.Log("No file");
}
XSSFWorkbook workbook = new XSSFWorkbook(Application.dataPath+ "/Resources/Data/Test.xlsx");
ISheet testSheet = workbook.GetSheet("Test");
IEnumerator iter = testSheet.GetRowEnumerator();
while (iter.MoveNext())
{
IRow row = iter.Current as IRow;
string line = "";
foreach(ICell cell in row.Cells)
{
switch (cell.CellType)
{
case CellType.String:
line += cell.StringCellValue;
break;
case CellType.Numeric:
line += cell.NumericCellValue;
break;
case CellType.Boolean:
line += cell.BooleanCellValue;
break;
case CellType.Error:
line += cell.ErrorCellValue;
break;
}
line += "[" + cell.CellType + "]"+ (cell.CellComment!=null ? ("("+cell.CellComment.String.String+")"):"")+"\t";
}
Debug.Log(line);
}
}
public void TestMergingOverlappingCols_OVERLAPS_2_MINOR()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
CT_Col col = cols.AddNewCol();
col.min = (2 + 1);
col.max = (4 + 1);
col.width = (20);
col.customWidth = (true);
sheet.GroupColumn((short)1, (short)3);
cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "testMergingOverlappingCols_OVERLAPS_2_MINOR/cols:" + cols);
Assert.AreEqual(1, cols.GetColArray(0).outlineLevel);
Assert.AreEqual(2, cols.GetColArray(0).min); // 1 based
Assert.AreEqual(2, cols.GetColArray(0).max); // 1 based
Assert.AreEqual(false, cols.GetColArray(0).customWidth);
Assert.AreEqual(1, cols.GetColArray(1).outlineLevel);
Assert.AreEqual(3, cols.GetColArray(1).min); // 1 based
Assert.AreEqual(4, cols.GetColArray(1).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(1).customWidth);
Assert.AreEqual(0, cols.GetColArray(2).outlineLevel);
Assert.AreEqual(5, cols.GetColArray(2).min); // 1 based
Assert.AreEqual(5, cols.GetColArray(2).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(2).customWidth);
Assert.AreEqual(3, cols.sizeOfColArray());
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testMergingOverlappingCols_OVERLAPS_2_MINOR");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 2; i <= 4; i++)
{
Assert.AreEqual(20 * 256, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
Assert.AreEqual(sheet.DefaultColumnWidth * 256, sheet.GetColumnWidth(1), "Unexpected width of column " + 1);
}
public void abc(String FilePath)
{
XSSFWorkbook hssfwb;
using (FileStream file = new FileStream(FilePath), FileMode.Open, FileAccess.Read))
{
hssfwb = new XSSFWorkbook(file);
}
ISheet sheet = hssfwb.GetSheet("Sheet1");
for (int row = 0; row <= sheet.LastRowNum+1; row++)
{
if (sheet.GetRow(row) != null)
{
public void TestNoColsWithoutWidthWhenGrouping()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)7);
sheet.GroupColumn((short)9, (short)12);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGrouping");
sheet = (XSSFSheet)wb.GetSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186/cols:" + cols);
foreach (CT_Col col in cols.GetColArray())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
public void TestNoColsWithoutWidthWhenGrouping()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)7);
sheet.GroupColumn((short)9, (short)12);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGrouping");
sheet = (XSSFSheet)wb.GetSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186/cols:" + cols);
foreach (CT_Col col in cols.GetColList())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
public static void ExportHandleNumExcel(Dictionary<string, Model.DTO.HKHandleNumDetail> hkHandleNumDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号6 香港新马寄出个数量化单（5.5之后）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("香港新马");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.GetRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚网申3");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(hkHandleNumDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.HKHandleNumDetail item in hkHandleNumDetails.Values)
{
row = sheet.CreateRow(4 + i);
row.HeightInPoints = 25;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//申请学历
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.Education);
//学校申请
cell = row.CreateCell(4);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Application);
//新加坡公立硕士
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.HK.SingaporeMaster);
//香港博士
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.HK.Doctor);
//签证申请
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Visa);
//资深文案
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//寄出日期
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
//备注
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(4 + hkHandleNumDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(7);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
row = sheet.CreateRow(5 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1、新马申请学历：幼稚园、小学、O Level、A Level、中学、预科、专科、本科、硕士、博士、语言");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2、香港申请学历：本科、硕士和博士");
cell.CellStyle = style;
row = sheet.CreateRow(7 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3、量化手写无效；需要机打。");
cell.CellStyle = style;
row = sheet.CreateRow(8 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、寄出后2个工作日内填报量化。");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("5、新加坡公立硕士以及香港博士申请一个合同号算2个寄出个数。");
cell.CellStyle = style;
row = sheet.CreateRow(10 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("6、未精益，只填报制作文案即可。");
cell.CellStyle = style;
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
/// <summary>
/// 功能：由Excel(2007)导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表名称(默认为NULL)</param>
/// <param name="headerRowIndex">Excel表头行索引(默认为0)</param>
/// <param name="error">出错信息</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToDataTable(string excelFilePath, string sheetName, int? headerRowIndex, ref string error)
{
if (System.IO.File.Exists(excelFilePath) == true)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
DataTable dataTable = new DataTable();
try
{
XSSFWorkbook workbook = new XSSFWorkbook(stream);
XSSFSheet sheet;
XSSFRow headerRow;
if (string.IsNullOrEmpty(sheetName) == true)
{
sheet = (XSSFSheet)workbook.GetSheetAt(0);
}
else
{
sheet = (XSSFSheet)workbook.GetSheet(sheetName);
}
if (headerRowIndex == null)
{
headerRow = (XSSFRow)sheet.GetRow(0);
}
else
{
headerRow = (XSSFRow)sheet.GetRow((int)headerRowIndex);
}
int cellCount = headerRow.LastCellNum;
//添加列名
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dataTable.Columns.Add(column);
}
//添加数据
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) == null)
{
dataRow[j] = null;
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dataTable.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return dataTable;
}
catch (Exception ex)
{
error = "由Excel导入DataTable(ex)" + ex.ToString();
throw;
}
}
}
else
{
return null;
}
}
public ActionResult DownloadAsExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
TempData["Message"] = "Unable to download file. No file was selected. Please select a file and try again.";
return RedirectToAction("Index");
}
try
{
var file = GetNamedFile(id);
var created = file.TimeStamp;
var filePathAndFilename = file.FilePath;
var filename = file.FileNameLessExtension;
var streamReader = new StreamReader(filePathAndFilename);
var engine = new FileHelperEngine<FeederSystemFixedLengthRecord>();
var result = engine.ReadStream(streamReader);
var transactions = result.ToList();
// Opening the Excel template...
var templateFileStream = new FileStream(Server.MapPath(@"~\Files\RevisedScrubberWithoutData.xlsx"),
FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
var templateWorkbook = new XSSFWorkbook(templateFileStream);
// Getting the worksheet by its name...
var sheet = templateWorkbook.GetSheet("Sheet1");
// We need this so the date will be formatted correctly; otherwise, the date format gets all messed up.
var dateCellStyle = templateWorkbook.CreateCellStyle();
var format = templateWorkbook.CreateDataFormat();
dateCellStyle.DataFormat = format.GetFormat("[$-809]m/d/yyyy;@");
// Here's another to ensure we get a number with 2 decimal places:
var twoDecimalPlacesCellStyle = templateWorkbook.CreateCellStyle();
format = templateWorkbook.CreateDataFormat();
twoDecimalPlacesCellStyle.DataFormat = format.GetFormat("#0.00");
var boldFont = templateWorkbook.CreateFont();
boldFont.FontHeightInPoints = 11;
boldFont.FontName = "Calibri";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
var boldCellStyle = templateWorkbook.CreateCellStyle();
boldCellStyle.SetFont(boldFont);
var boldTotalAmountStyle = templateWorkbook.CreateCellStyle();
boldTotalAmountStyle.DataFormat = twoDecimalPlacesCellStyle.DataFormat;
boldTotalAmountStyle.SetFont(boldFont);
var grandTotal = 0.0;
var i = 0;
foreach (var transaction in transactions)
{
i++;
// Getting the row... 0 is the first row.
var dataRow = sheet.GetRow(i);
dataRow.CreateCell(0).SetCellValue(transaction.FiscalYear);
dataRow.CreateCell(1).SetCellValue(transaction.ChartNum);
dataRow.CreateCell(2).SetCellValue(transaction.Account);
dataRow.CreateCell(3).SetCellValue(transaction.SubAccount);
dataRow.CreateCell(4).SetCellValue(transaction.ObjectCode);
dataRow.CreateCell(5).SetCellValue(transaction.SubObjectCode);
dataRow.CreateCell(6).SetCellValue(transaction.BalanceType);
dataRow.CreateCell(7).SetCellValue(transaction.ObjectType.Trim());
dataRow.CreateCell(8).SetCellValue(transaction.FiscalPeriod);
dataRow.CreateCell(9).SetCellValue(transaction.DocumentType);
dataRow.CreateCell(10).SetCellValue(transaction.OriginCode);
dataRow.CreateCell(11).SetCellValue(transaction.DocumentNumber);
dataRow.CreateCell(12).SetCellValue(transaction.LineSequenceNumber);
dataRow.CreateCell(13).SetCellValue(transaction.TransactionDescription);
var transactionAmount = Convert.ToDouble(transaction.Amount.Trim());
grandTotal += transactionAmount;
var cell = dataRow.CreateCell(14);
cell.CellStyle = twoDecimalPlacesCellStyle;
cell.SetCellValue(transactionAmount);
dataRow.CreateCell(15).SetCellValue(transaction.DebitCreditCode.Trim());
cell = dataRow.CreateCell(16);
cell.CellStyle = dateCellStyle;
cell.SetCellValue(Convert.ToDateTime(transaction.TransactionDate));
dataRow.CreateCell(17).SetCellValue(transaction.OrganizationTrackingNumber);
dataRow.CreateCell(18).SetCellValue(transaction.ProjectCode);
dataRow.CreateCell(19).SetCellValue(transaction.OrganizationReferenceId.Trim());
dataRow.CreateCell(20).SetCellValue(transaction.ReferenceTypeCode.Trim());
dataRow.CreateCell(21).SetCellValue(transaction.ReferenceOriginCode.Trim());
dataRow.CreateCell(22).SetCellValue(transaction.ReferenceNumber.Trim());
dataRow.CreateCell(23).SetCellValue(transaction.ReversalDate.Trim());
dataRow.CreateCell(24).SetCellValue(transaction.TransactionEncumbranceUpdateCode.Trim());
}
if (transactions.Any())
{
var totalsRow = sheet.GetRow(i + 1);
var totalsCell = totalsRow.CreateCell(13);
totalsCell.CellStyle = boldCellStyle;
totalsCell.SetCellValue(" Grand Total");
totalsCell = totalsRow.CreateCell(14);
totalsCell.CellStyle = boldTotalAmountStyle;
totalsCell.SetCellValue(grandTotal);
}
// Forcing formula recalculation...
sheet.ForceFormulaRecalculation = true;
var ms = new MemoryStream();
// Writing the workbook content to the FileStream...
templateWorkbook.Write(ms);
TempData["Message"] = "Excel report created successfully!";
// Sending the server processed data back to the user computer...
return File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename + ".xlsx");
}
catch (Exception ex)
{
TempData["Message"] = String.Format("Opps! Something went wrong: {0}", ex.Message);
return RedirectToAction("Index");
}
}
/// <summary>
///
/// </summary>
/// <param name="filename"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public bool SaveTransactionsFromFile(string filename, string sheetName)
{
try
{
//Using Npoi instead of the office components
ISheet sheet;
if (filename.Substring(filename.LastIndexOf('.')).ToLower() == EXCEL_2007_EXTENSION)
{
XSSFWorkbook workbook;
using (var file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);
}
sheet = workbook.GetSheet(sheetName);
}
else
{
HSSFWorkbook workbook;
using (var file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);
}
sheet = workbook.GetSheet(sheetName);
}
var transactionsList = new List<dynamic>();
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
var theRow = sheet.GetRow(row);
var transaction = new
{
campana = theRow.GetCell(0).StringCellValue,
factura = theRow.GetCell(1).StringCellValue,
fecha = theRow.GetCell(2).DateCellValue,
monto = theRow.GetCell(3).NumericCellValue,
puntos = theRow.GetCell(4).NumericCellValue,
comision = theRow.GetCell(5).NumericCellValue,
vendedor = theRow.GetCell(6).StringCellValue,
};
transactionsList.Add(transaction);
}
}
//string connectionString =
// string.Format(
// filename.Substring(filename.LastIndexOf('.')).ToLower() == EXCEL_2007_EXTENSION
// ? EXCEL_2007_CONNECTION_STRING
// : EXCEL_2005_CONNECTION_STRING, filename);
//var adapter = new OleDbDataAdapter(string.Format(SELECT_ALL_QUERY, sheetName), connectionString);
//var dataSet = new DataSet();
//adapter.Fill(dataSet, DATA_TABLE_NAME);
//var reportData = dataSet.Tables[DATA_TABLE_NAME].AsEnumerable();
//var transactionsList =
// reportData.Where(y => y.Field<string>("Campaña") != null)
// .Select(
// x => new
// {
// campana = x.Field<string>("Campaña"),
// factura = x.Field<string>("Factura"),
// fecha = x.Field<DateTime>("Fecha"),
// monto = x.Field<double>("Monto"),
// puntos = x.Field<double>("Puntos"),
// comision = x.Field<double>("Comision"),
// vendedor = x.Field<string>("Vendedor"),
// }).
// ToList(); //o por nombre de columna.
foreach (var individualTransaction in transactionsList)
{
int cedNumber = GetCedNumberFromString(individualTransaction.vendedor);
var customer = _usersRepository.GetUserByIdentificationNumber(cedNumber);
var company = _companiesRepository.GetCompany(individualTransaction.campana);
var transaction = new Transaction
{
Amount = individualTransaction.monto,
BillBarCode = individualTransaction.factura,
UserId = customer.UserId,
Points = (int) individualTransaction.puntos,
TransactionDate = Convert.ToDateTime(individualTransaction.fecha),
CompanyId = company.CompanyId,
CreatetedAt = DateTime.Now,
UpdatedAt = DateTime.Now,
Comision = individualTransaction.comision,
};
if (_transactionsRepository.SaveTransaction(transaction))
{
if (!DistributeTransactionCashback(transaction))
{
_transactionsRepository.RejectChanges();
}
}
else
{
//send error
_usersRepository.RejectChanges();
_transactionsRepository.RejectChanges();
return false;
}
}
_transactionsRepository.SaveChangesMade();
_usersRepository.SaveChangesMade();
return true;
}
catch (Exception ex)
{
//TODO: enviar algun error
_usersRepository.RejectChanges();
_transactionsRepository.RejectChanges();
Console.WriteLine(ex.Message);
return false;
}
}
public static bool Import(string TargetFolder)
{
bool Success = true;
string[] Files = Directory.GetFiles(TargetFolder);
List<string> ErrorList = new List<string>();
foreach (string CurrentFile in Files)
{
try
{
XSSFWorkbook MyWorkbook = null;
using (FileStream file = new FileStream(CurrentFile, FileMode.Open, FileAccess.Read))
{
int Row = 0;
MyWorkbook = new XSSFWorkbook(file);
// Read file values here
#region Biographical
try
{
string BioDataCapturer = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(0).StringCellValue;
string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(1).NumericCellValue.ToString();
string BioName = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(2).StringCellValue;
string BioSurname = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(3).StringCellValue;
string BioLatitude = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(4).StringCellValue;
string BioLongitude = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(5).StringCellValue;
string BioIDNumber = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(6).StringCellValue;
string BioClinic = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(7).StringCellValue;
string BioDateOfBirth = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(8).DateCellValue.ToString();
string BioMale = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(9).StringCellValue;
string BioFemale = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(10).StringCellValue;
string BioContactNo = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(11).NumericCellValue.ToString();
string BioFileNo = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(12).StringCellValue;
string BioNextOfKinRelationship = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(13).StringCellValue;
string BioNextOfKinName = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(14).StringCellValue;
string BioNextOfKinTelephone = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(15).NumericCellValue.ToString();
string BioHPT = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(17).StringCellValue;
string BioDiabetes = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(18).StringCellValue;
string BioEpilepsy = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(19).StringCellValue;
string BioAsthma = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(20).StringCellValue;
string BioHIV = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(21).StringCellValue;
string BioTB = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(22).StringCellValue;
string BioMaternalHealth = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(23).StringCellValue;
string BioChildHealth = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(24).StringCellValue;
string BioEpilepsy2 = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(25).StringCellValue;
string BioOther = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(26).StringCellValue;
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Biographical: " + ex.Message);
}
# endregion
#region Visit Data
List<ArrayList> VisitDataEntries = new List<ArrayList>();
Row = 2;
try
{
while (MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990,1,1))
{
ArrayList VisitDataEntriesCurrent = new ArrayList();
// Index
// 0 - Date of Visit
// 1 - Weight
// 2 - Height
// 3 - BMI
// 4 - Next Visit Date
// 5 - HPT
// 6 - Diabetes
// 7 - Epilepsy
// 8 - Asthma
// 9 - HIV
// 10 - TB
// 11 - Mat Hlth
// 12 - Child Hlth
// 13 - Epilepsy
// 14 - Other
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(0).DateCellValue.ToString());
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(1).NumericCellValue.ToString());
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(2).NumericCellValue.ToString());
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(3).NumericCellValue.ToString());
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(5).DateCellValue.ToString());
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(7).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(8).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(9).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(10).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(11).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(12).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(13).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(14).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(15).StringCellValue);
VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(16).StringCellValue);
VisitDataEntries.Add(VisitDataEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Visit data: " + ex.Message);
}
#endregion
#region Hypertension
List<ArrayList> HypertensionEntries = new List<ArrayList>();
Row = 5;
try
{
while (MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList HypertensionEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// 1 - DWF Referral
// 2 - Diagnosed and given treatment systolic
// 3 - Diagnosed and given treatment diastolic
// 4 - Not put on meds systolic
// 5 - Not put on meds diastolic
// 6 - Next review date
// 7 - On meds systolic
// 8 - On meds diastolic
// 9 - Blood sugar level
// 10 - Results Creatinine
// 11 - Results Cholesterol
// 12 - Treatment
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(0).DateCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(1).StringCellValue);
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(2).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(3).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(4).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(5).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(6).DateCellValue);
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(7).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(8).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(10).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(12).NumericCellValue.ToString());
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(13).StringCellValue);
HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(14).StringCellValue);
HypertensionEntries.Add(HypertensionEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Hypertension: " + ex.Message);
}
#endregion
#region Diabetes
List<ArrayList> DiabetesEntries = new List<ArrayList>();
Row = 4;
try
{
while (MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList DiabetesEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// Blood sugar levels
// 1 - DWF referral
// 2 - Diagnosed & given treatment
// 3 - Not on meds Blood Sugar level
// 4 - Next review date
// 5 - On meds Blood Sugar level
// BP Reading
// 6 - Systolic
// 7 - Diastolic
// Results
// 8 - HbA1C
// 9 - Creatinine
// 10 - Cholesterol
// 11 - Foot exam
// 12 - Eye test
//
// 13 - Refer to clinic
// 14 - Referral No.
//
// 15 - Treatment
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(0).DateCellValue.ToString());
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(1).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(2).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(3).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(4).DateCellValue.ToString());
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(5).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(7).DateCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(8).NumericCellValue.ToString());
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(10).NumericCellValue.ToString());
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(11).NumericCellValue.ToString());
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(12).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(13).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(14).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(15).StringCellValue);
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(16).NumericCellValue.ToString());
DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(17).StringCellValue);
DiabetesEntries.Add(DiabetesEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Diabetes: " + ex.Message);
}
#endregion
#region Epilepsy
List<ArrayList> EpilepsyEntries = new List<ArrayList>();
Row = 5;
try
{
while (MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList EpilepsyEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// Checks
// 1 - DWF referral
// 2 - No. of fits in last month
// 3 - Drug side-effects if any
// BP Reading
// 4 - Systolic
// 5 - Diastolic
//
// 6 - Treatment
EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(0).DateCellValue.ToString());
EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(1).StringCellValue);
EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(2).NumericCellValue.ToString());
EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(3).StringCellValue);
EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(5).NumericCellValue.ToString());
EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(6).NumericCellValue.ToString());
EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(8).StringCellValue);
EpilepsyEntries.Add(EpilepsyEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Epilepsy: " + ex.Message);
}
#endregion
#region Asthma
List<ArrayList> AsthmaEntries = new List<ArrayList>();
Row = 5;
try
{
while (MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList AsthmaEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// Results
// 1 - DWF referral
// 2 - Peak Expiratory Flow Rate
// BP Reading
// 3 - Systolic
// 4 - Diastolic
//
// 5 - Treatment
AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(0).DateCellValue.ToString());
AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(1).StringCellValue);
AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(2).StringCellValue);
AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(4).NumericCellValue.ToString());
AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(5).NumericCellValue.ToString());
AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(6).StringCellValue);
AsthmaEntries.Add(AsthmaEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Asthma: " + ex.Message);
}
#endregion
#region HIV
List<ArrayList> HIVEntries = new List<ArrayList>();
Row = 3;
try
{
while (MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList HIVEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// Results
// 1 - DWF referral
// 2 - CD4
// 3 - Viral Load
//
// 4 - Treatment
// BP Reading
// 5 - Systolic
// 6 - Diastolic
HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(0).DateCellValue.ToString());
HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(1).StringCellValue);
HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(2).NumericCellValue.ToString());
HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(3).NumericCellValue.ToString());
HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(4).StringCellValue);
HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(6).NumericCellValue.ToString());
HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(7).NumericCellValue.ToString());
HIVEntries.Add(HIVEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - HIV: " + ex.Message);
}
#endregion
#region TB
List<ArrayList> TBEntries = new List<ArrayList>();
Row = 3;
try
{
while (MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList TBEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// 1 - DWF referral
// Check
// 2 - Sputum Taken
// Test Results Review
// 3 - Date
// Results
// 4 - Genexpert
// 5 - AFB
// 6 - Treatment
TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(0).DateCellValue.ToString());
TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(1).StringCellValue);
TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(2).StringCellValue);
TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(3).DateCellValue.ToString());
TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(5).StringCellValue);
TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(6).StringCellValue);
TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(7).StringCellValue);
TBEntries.Add(TBEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - TB: " + ex.Message);
}
#endregion
#region Mat Health
List<ArrayList> MatHealthEntries = new List<ArrayList>();
Row = 3;
try
{
while (MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList MatHealthEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// 1 - DWF referral
// 2 - Mom Connect Registered
// Monthly ANC visit
// 3 - ANC visit no
// PNC Visits
// 4 - PNC 1-week
// 5 - PCR done
// 6 - PNC 6-weeks
MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(0).DateCellValue.ToString());
MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(1).StringCellValue);
MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(2).StringCellValue);
MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(3).NumericCellValue.ToString());
MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(4).StringCellValue);
MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(5).StringCellValue);
MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(6).StringCellValue);
MatHealthEntries.Add(MatHealthEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Mat Health: " + ex.Message);
}
#endregion
#region Child Health
List<ArrayList> ChildHealthEntries = new List<ArrayList>();
Row = 2;
try
{
while (MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList ChildHealthEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// 1 - DWF referral
// 2 - PCR done
// 3 - Current RTHC?
// 4 - Vaccinations up to date
ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(0).DateCellValue.ToString());
ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(1).StringCellValue);
ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(2).StringCellValue);
ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(3).StringCellValue);
ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(4).StringCellValue);
ChildHealthEntries.Add(ChildHealthEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Child Health: " + ex.Message);
}
#endregion
#region Other
List<ArrayList> OtherEntries = new List<ArrayList>();
Row = 5;
try
{
while (MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1))
{
ArrayList OtherEntriesCurrent = new ArrayList();
// Index
// 0 - Visit date
// 1 - DWF referral
// Other Condition
// 2 - Condition that required referral
// 3 - Outcome
// BP Reading
// 4 - Systolic
// 5 - Diastolic
OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(0).DateCellValue.ToString());
OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(1).StringCellValue);
OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(3).StringCellValue);
OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(4).StringCellValue);
OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(6).NumericCellValue.ToString());
OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(7).NumericCellValue.ToString());
OtherEntries.Add(OtherEntriesCurrent);
Row++;
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + " - Other: " + ex.Message);
}
#endregion
// Queries here
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(CurrentFile + ": " + ex.Message);
}
}
if (Success == false)
{
System.Windows.MessageBox.Show("Some errors occurred. Please check the log file for a list of errors.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information);
File.WriteAllLines(Directory.GetCurrentDirectory() + "ImportErrorLog.txt", ErrorList);
System.Diagnostics.Process.Start(Directory.GetCurrentDirectory() + "ImportErrorLog.txt");
}
else
System.Windows.MessageBox.Show(Files.Length.ToString() + " file(s) have been successfully imported.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information);
return Success;
}
public static void ExportUKExcel(Dictionary<string, Model.DTO.UKHandleNumDetail> ukHandleNumDetails, Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单（2015年1月1日后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("个数单");
//下面开始导出个数
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
IRow row = sheet.GetRow(3);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(12);
cell.SetCellValue(ukHandleNumDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleNumDetail item in ukHandleNumDetails.Values)
{
row = sheet.CreateRow(6 + i);
row.HeightInPoints = 16.5F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//申请学历
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.Education);
//前四
cell = row.CreateCell(4);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.FirstFour);
//博士
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.Doctor);
//国内合作
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.Cooperation);
//申请个数
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.ApplyNum);
//网申个数
cell = row.CreateCell(8);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.OnlineNum);
//文书个数
cell = row.CreateCell(9);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.PSNum);
//资深文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//高级文书文案
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.OldSenior);
//文书文案
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.OldAuthor);
//转案区间
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(15);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(6 + ukHandleNumDetails.Count);
row.HeightInPoints = 12;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
row = sheet.CreateRow(7+ ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1、申请学历填写标准的申请类别");
cell.CellStyle = style;
row = sheet.CreateRow(8 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2、量化手写无效；需要机打。");
cell.CellStyle = style;
row = sheet.CreateRow(9 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3、寄出后2个工作日内填报量化。");
cell.CellStyle = style;
row = sheet.CreateRow(10 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、博士申请及英国前四所本硕申请一个合同号算2个寄出个数，国内合作一个合同号算0.5个寄出个数。");
cell.CellStyle = style;
//下面开始导出量化
sheet = workbook.GetSheet("量化单");
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.GetRow(3);
cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(11);
cell.SetCellValue(ukHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values)
{
row = sheet.CreateRow(6 + i);
row.HeightInPoints = 16.7F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//套磁
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Magnetic);
//文书
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.PS);
//录取
cell = row.CreateCell(8);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//资深文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//文书文案
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//转案区间
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(6 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(7 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
row = sheet.CreateRow(8 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
row = sheet.CreateRow(9 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("3、申请学历填写标准的申请类别");
cell.CellStyle = style;
//下面开始保存
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单（2015年1月1日后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("量化单");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
IRow row = sheet.GetRow(3);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(11);
cell.SetCellValue(ukHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values)
{
row = sheet.CreateRow(5 + i);
row.HeightInPoints = 16.7F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//套磁
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Magnetic);
//文书
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.PS);
//录取
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//资深文案
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//文书文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//转案区间
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(5 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//设置合并单元格区域
CellRangeAddress cellRangeAddress = new CellRangeAddress(7 + ukHandleQuanDetails.Count, 7 + ukHandleQuanDetails.Count, 0, 12);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(7 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
//设置合并单元格区域
cellRangeAddress = new CellRangeAddress(8 + ukHandleQuanDetails.Count, 8 + ukHandleQuanDetails.Count, 0, 12);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(8 + ukHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
cell.CellStyle.Alignment = HorizontalAlignment.Left;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
//设置合并单元格区域
cellRangeAddress = new CellRangeAddress(9 + ukHandleQuanDetails.Count, 9 + ukHandleQuanDetails.Count, 0, 1);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(9 + ukHandleQuanDetails.Count);
row.HeightInPoints = 37.5F;
cell = row.CreateCell(0);
cell.SetCellValue("3、申请学历填写标准的申请类别");
cell.CellStyle = style;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
////设置单元格格式
//style = workbook.CreateCellStyle();
//style.Alignment = HorizontalAlignment.Center;
//style.VerticalAlignment = VerticalAlignment.Center;
//style.BorderTop = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderBottom = BorderStyle.Thin;
//font = workbook.CreateFont();
//font.FontName = "宋体";
//font.FontHeightInPoints = 11;
//style.SetFont(font);
//cell = row.CreateCell(2);
//cell.SetCellValue("高中及以下（无文书）");
//style.WrapText = true;
//cell.CellStyle = style;
//cell = row.CreateCell(3);
//cell.SetCellValue("高端本科");
//cell.CellStyle = style;
//cell = row.CreateCell(4);
//cell.SetCellValue("本科预科/硕士预科（无文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(5);
//cell.SetCellValue("非高端本科院校");
//cell.CellStyle = style;
//cell = row.CreateCell(6);
//cell.SetCellValue("高端硕士院校");
//cell.CellStyle = style;
//cell = row.CreateCell(7);
//cell.SetCellValue("非高端研究生");
//cell.CellStyle = style;
//cell = row.CreateCell(8);
//cell.SetCellValue("博士");
//cell.CellStyle = style;
//cell = row.CreateCell(9);
//cell.SetCellValue("只申请语言学校");
//cell.CellStyle = style;
//cell = row.CreateCell(10);
//cell.SetCellValue("高中及以下（有文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(11);
//cell.SetCellValue("本科预科/硕士预科（有文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(12);
//cell.CellStyle = style;
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleNumDTO> hnDTOList, string fileName)
{
if (hnDTOList == null)
{
return string.Empty;
}
string url = "http://blessing.wang/ExcelModel/numModel.xlsx";
WebRequest request = WebRequest.Create(url);
WebResponse response = request.GetResponse();
Stream stream = response.GetResponseStream();
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("寄出个数");
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(1);
cell.SetCellValue(hnDTOList.Count + "个");
row = sheet.GetRow(2);
cell = row.GetCell(7);
cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
//设置单元格格式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int i = 0; i < hnDTOList.Count; i++)
{
row = sheet.CreateRow(5 + i);
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].ContractNo);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Student);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Application);
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Visa);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Author);
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].FirstFour);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Doctor);
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Cooperation);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Note);
}
style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(5 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("编制人签名:");
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue("经理审核签名：");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("总经理/分管总裁批准签字：");
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue("保持：人力资源中心");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1．《文案工作进度（量化）日报单》汇总动态记录PM-16《文案工作量化日报表》；此单必须机打，手写量化将直接作废；");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(7 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2．本报单请于16:00之前申报， □确认√确认填报项。请勿集中中延期申报");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(8 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3．高端合同（英国前四所大学、博士）需在“英国前四所大学”、“博士”项点黑");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(9 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4．国内合作合同需在“国内合作”项点黑");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
string path = HttpContext.Current.Server.MapPath("/ExportTemp/");
DirectoryInfo info = new DirectoryInfo(path);
FileInfo[] files = info.GetFiles();
if (files.Length > 0)
{
for (int i = files.Length - 1; i >= 0; i--)
{
files[i].Delete();
}
}
fileName += ".xlsx";
FileStream file = File.Create(path + fileName);
workbook.Write(file);
file.Close();
stream.Close();
return "http://blessing.wang/ExportTemp/" + fileName;
}
private void button2_Click(object sender, EventArgs e)
{
string ruta = this.buscarDoc();
ConexionPostgres conn = new ConexionPostgres();
FileStream fs;
try
{
fs = new FileStream(ruta, FileMode.Open, FileAccess.Read);
}
catch
{
MessageBox.Show("El archivo está siendo usado por otro programa. Asegurese de haber seleccionado el archivo correcto.");
return;
}
XSSFWorkbook wb = new XSSFWorkbook(fs);
string nombreHoja = "";
for (int i = 0; i < wb.Count; i++)
{
nombreHoja = wb.GetSheetAt(i).SheetName;
}
XSSFSheet sheet = (XSSFSheet)wb.GetSheet(nombreHoja);
try
{
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
var fila = sheet.GetRow(row);
var nit = fila.GetCell(0).ToString();
var numero_unidad = fila.GetCell(1).ToString();
var nombre_completo = fila.GetCell(2).ToString();
var coeficiente = fila.GetCell(3).ToString().Replace(",", ".");
var documento = fila.GetCell(4).ToString();
var cadenaSql = string.Format(
@"INSERT INTO modelo.unidad_residencial
(
nit,
numero_unidad,
nombre_completo,
coeficiente,
documento
)
VALUES
(
'{0}',
'{1}',
'{2}',
'{3}',
'{4}'
);",
nit,
numero_unidad,
nombre_completo,
coeficiente,
documento
);
var resultado = conn.registrar(cadenaSql);
if (!resultado)//Falló la consulta
{
MessageBox.Show("El registro NIT:" + nit
+ ", Número Unidad:" + numero_unidad
+ ", Nombre Completo:" + nombre_completo
+ ", Coeficiente:" + coeficiente
+ ", Número Documento:" + documento
+ " ,no pudo ser completado de manera exitosa, revise los datos (están mal o ya existen o se encontro elemento en blanco).");
MessageBox.Show(cadenaSql);
return;
}
}
else
{
MessageBox.Show("realizado");
}
}
MessageBox.Show("Datos cargados correctamente.");
}
catch
{
MessageBox.Show("Algo pasó con la carga de archivos, asegúrese de que ha cargado el archivo correcto con datos válidos.");
}
//Se cierra el archivo
fs.Close();
}
public void SetUp()
{
if (workbook == null)
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(SS.FILENAME);
OPCPackage pkg = OPCPackage.Open(is1);
workbook = new XSSFWorkbook(pkg);
sheet = workbook.GetSheet(SS.TEST_SHEET_NAME);
}
_functionFailureCount = 0;
_functionSuccessCount = 0;
_EvaluationFailureCount = 0;
_EvaluationSuccessCount = 0;
}
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.HKHandleQuanDetail> hkHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号12 香港新马激励量化单（5.5日之后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("香港新马");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.GetRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚网申3");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(hkHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.HKHandleQuanDetail item in hkHandleQuanDetails.Values)
{
row = sheet.CreateRow(4 + i);
row.HeightInPoints = 25;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//翻译
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Translation);
//签证寄出
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Visa);
//寄出日期
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
//资深文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//新马文案
cell = row.CreateCell(11);
cell.CellStyle = style;
//录取
cell = row.CreateCell(12);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//获签
cell = row.CreateCell(13);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Sign);
//文书文案
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(15);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//备注
cell = row.CreateCell(16);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(4 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
//style.BorderTop = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
////设置合并单元格区域
//CellRangeAddress cellRangeAddress = new CellRangeAddress(5 + hkHandleQuanDetails.Count, 5 + hkHandleQuanDetails.Count, 0, 15);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(5 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
////设置合并单元格区域
//cellRangeAddress = new CellRangeAddress(6 + hkHandleQuanDetails.Count, 6 + hkHandleQuanDetails.Count, 0, 15);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(6 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
cell.CellStyle.Alignment = HorizontalAlignment.Left;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
//设置合并单元格区域
//cellRangeAddress = new CellRangeAddress(7 + hkHandleQuanDetails.Count, 7 + hkHandleQuanDetails.Count, 0, 1);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(7 + hkHandleQuanDetails.Count);
//row.HeightInPoints = 37.5F;
cell = row.CreateCell(0);
cell.SetCellValue("3、新加坡量化类别：");
cell.CellStyle = style;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
row = sheet.CreateRow(8 + hkHandleQuanDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、香港量化类别：");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hkHandleQuanDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("5、马来西亚量化类别：");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.GetRow(7 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("公立硕士");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("幼儿园，小学，公立大学，大专、本科、语言/预科/私立大专，本科、研究生");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("NEXUS/BC幼儿园");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("visa only");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.CellStyle = style;
row = sheet.GetRow(8 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("博士/研究生");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("PS（1封）+推荐信（2封）+CV（1封）");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("个人陈述PS（1封）");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("推荐信（2封）");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("CV（1封）");
cell.CellStyle = style;
row = sheet.GetRow(9 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("公立本科");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("公立硕士");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("语言/预科/私立本科、研究生");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("家长旅游签");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.CellStyle = style;
sheet.AutoSizeColumn(4);
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public static bool Import(string TargetFolder)
{
bool Success = true;
string[] Files = Directory.GetFiles(TargetFolder);
List<string> ErrorList = new List<string>();
foreach (string CurrentFile in Files)
{
try
{
XSSFWorkbook MyWorkbook = null;
using (FileStream file = new FileStream(CurrentFile, FileMode.Open, FileAccess.Read))
{
MyWorkbook = new XSSFWorkbook(file);
// Read file values here
#region Biographical
string BioChowName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(0).StringCellValue;
string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(1).StringCellValue;
string BioFirstName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(2).StringCellValue;
string BioSecondName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(3).StringCellValue;
string BioIDNumber = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(4).StringCellValue;
string BioGPSLatitude = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(5).StringCellValue;
string BioGPSLongitude = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(6).StringCellValue;
string BioArea = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(7).StringCellValue;
string BioClinic = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(8).StringCellValue;
#endregion
#region VisitDetails
// VD prefix used to identify variables associated with Visit Details tab
string VDVisitNum = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(0).StringCellValue;
string VDVisitDate = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(1).StringCellValue;
string VDNextVisitDate = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(2).StringCellValue;
string VDOutcome = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(4).StringCellValue;
string VDHPT = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(5).StringCellValue;
string VDDiabetes = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(6).StringCellValue;
string VDEpilepsy = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(7).StringCellValue;
string VDHIV = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(8).StringCellValue;
string VDTB = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(9).StringCellValue;
string VDMatHealth = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(10).StringCellValue;
string VDChildHealth = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(11).StringCellValue;
string VDOther = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(12).StringCellValue;
string VDODoorToDoor = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(13).StringCellValue;
#endregion
#region HyperTension
//Hyper prefix used to identify variables associated with Hypertension tab.
//HiEHRef = HiEH Referral
//ClinicRef = Clinic Referral
//AOT = Already On Treatment
string HyperDateOfVisit = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(0).StringCellValue;
string Hyper_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(1).StringCellValue;
string Hyper_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(2).StringCellValue;
string Hyper_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(3).StringCellValue;
string Hyper_HiEHRef_OnMeds = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(4).StringCellValue;
string Hyper_HiEHRef_StartDate = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(5).StringCellValue;
string Hyper_HiEHRef_BPReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(6).StringCellValue;
string Hyper_HiEHRef_TodayReadingTop = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(7).StringCellValue;
string Hyper_HiEHRef_TodayReadingBottom = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(8).StringCellValue;
string Hyper_HiEHRef_ReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(9).StringCellValue;
string Hyper_HiEHRef_RefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(10).StringCellValue;
string Hyper_ClinicRef_ReReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(12).StringCellValue;
string Hyper_ClinicRef_ReRefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(13).StringCellValue;
string Hyper_AOT_FollowUpReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(15).StringCellValue;
string Hyper_DoorToDoorReading_CheckReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(17).StringCellValue;
string Hyper_Medication = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(18).StringCellValue;
#endregion
#region Diabetes
//HiEHRef = HiEH Referral
//ClinicRef = Clinic Referral
//AOT = Already On Treatment
string DiabetesDateOfVisit = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(0).StringCellValue;
string Diabetes_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(1).StringCellValue;
string Diabetes_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(2).StringCellValue;
string Diabetes_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(3).StringCellValue;
string Diabetes_HiEHRef_OnMeds = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(4).StringCellValue;
string Diabetes_HiEHRef_StartDate = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(5).StringCellValue;
string Diabetes_HiEHRef_FollowUpTestReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(6).StringCellValue;
string Diabetes_HiEHRef_ReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(7).StringCellValue;
string Diabetes_HiEHRef_RefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(8).StringCellValue;
string Diabetes_ClinicRef_ReReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(10).StringCellValue;
string Diabetes_ClinicRef_ReRefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(11).StringCellValue;
string Diabetes_AOT_FollowUpReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(13).StringCellValue;
string Diabetes_DoorToDoorReading_CheckReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(15).StringCellValue;
string Diabetes_Medication = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(16).StringCellValue;
#endregion
#region Epilepsy
//HiEHRef = HiEH Referral
//CurrentRef = Current Referral
//OT = On Treatment
string EpilepsyDateOfVisit = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(0).StringCellValue;
string Epilepsy_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(1).StringCellValue;
string Epilepsy_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(2).StringCellValue;
string Epilepsy_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(3).StringCellValue;
string Epilepsy_CurrentRef_FitInLastMonth = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(5).StringCellValue;
string Epilepsy_CurrentRef_RefToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(6).StringCellValue;
string Epilepsy_CurrentRef_RefNum = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(7).StringCellValue;
string Epilepsy_OT_CurrentlyOnMeds = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(9).StringCellValue;
string Epilepsy_OT_StartDate = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(10).StringCellValue;
string Epilepsy_OT_MoreThanThreeFitsInLastMonth = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(11).StringCellValue;
string Epilepsy_OT_ReRefToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(12).StringCellValue;
string Epilepsy_OT_ReRefNum = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(13).StringCellValue;
string Epilepsy_Medication = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(14).StringCellValue;
#endregion
#region Asthma
//HiEHRef = HiEH Referral
//CurrentRef = Current Referral
//OT = On Treatment
string AsthmaDateOfVisit = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(0).StringCellValue;
string Asthma_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(1).StringCellValue;
string Asthma_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(2).StringCellValue;
string Asthma_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(3).StringCellValue;
string Asthma_CurrentRef_DifficultyBreathing = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(5).StringCellValue;
string Asthma_CurrentRef_RefToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(6).StringCellValue;
string Asthma_CurrentRef_RefNum = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(7).StringCellValue;
string Asthma_OT_CurrentlyOnMeds = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(9).StringCellValue;
string Asthma_OT_StartDate = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(10).StringCellValue;
string Asthma_OT_IncreaseInAttacks = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(11).StringCellValue;
string Asthma_OT_ReRefToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(12).StringCellValue;
string Asthma_OT_ReRefNum = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(13).StringCellValue;
string Asthma_Medication = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(14).StringCellValue;
#endregion
#region HIV
//HiEHRef = HiEH Referral
//ClinictRef = Clinic Referral
//IP = If Positive
//IN = If Negative
//IU = If Unknown
string HIVDateOfVisit = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(0).StringCellValue;
string HIV_HiEHRef_WentToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(1).StringCellValue;
string HIV_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(2).StringCellValue;
string HIV_HiEHRef_ReRefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(3).StringCellValue;
string HIV_ClinicRef_RefToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(5).StringCellValue;
string HIV_ClinicRef_RefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(6).StringCellValue;
string HIV_HIVStatus = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(8).StringCellValue;
string HIV_IP_OnARVs = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(10).StringCellValue;
string HIV_IP_StartDate = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(11).StringCellValue;
string HIV_IP_AdherenceOK = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(12).StringCellValue;
string HIV_IP_Concerns = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(13).StringCellValue;
string HIV_IP_RefToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(14).StringCellValue;
string HIV_IP_RefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(15).StringCellValue;
string HIV_IP_ARVsConcern = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(16).StringCellValue;
string HIV_IP_ReRefToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(17).StringCellValue;
string HIV_IP_ReRefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(18).StringCellValue;
string HIV_IN_CounselingDone = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(20).StringCellValue;
string HIV_IU_HIVTestDone = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(22).StringCellValue;
string HIV_HIVTest_HIVTestResults = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(24).StringCellValue;
string HIV_HIVTest_ReferToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(25).StringCellValue;
string HIV_HIVTest_RefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(26).StringCellValue;
string HIV_Medication = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(27).StringCellValue;
#endregion
#region TB
//HiEHRef = HiEH Referral
string TBDateOfVisit = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(0).StringCellValue;
string TB_HiEHRef_WentToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(1).StringCellValue;
string TB_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(2).StringCellValue;
string TB_HiEHRef_ReRefNum = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(3).StringCellValue;
string TB_SymptonsRefer_WeigthLoss = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(5).StringCellValue;
string TB_SymptonsRefer_SweatingAtNight = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(6).StringCellValue;
string TB_SymptonsRefer_FeverOverTwoWeeks = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(7).StringCellValue;
string TB_SymptonsRefer_CoughMoreTwoWeeks = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(8).StringCellValue;
string TB_SymptonsRefer_LossOfApetite = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(9).StringCellValue;
string TB_SymptonsRefer_RefToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(10).StringCellValue;
string TB_SymptonsRefer_RefNum = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(11).StringCellValue;
string TB_SymptonsRefer_Results = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(12).StringCellValue;
string TB_Treatment_NewlyDiagnosed = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(14).StringCellValue;
string TB_Treatment_StartDate = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(15).StringCellValue;
string TB_Treatment_RefContactsToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(16).StringCellValue;
string TB_Treatment_PrevOnMeds = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(17).StringCellValue;
string TB_Treatment_FinishDate = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(18).StringCellValue;
string TB_Treatment_Concerns = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(19).StringCellValue;
string TB_Treatment_RefToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(20).StringCellValue;
string TB_Treatment_RefNum = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(21).StringCellValue;
string TB_Medication = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(22).StringCellValue;
#endregion
#region MaternalHealth
//HiEHRef = HiEH Referral
//CP = Currently Pregnant
//PP = Possible Pregnancy
string MatHealthDateOfVisit = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(0).StringCellValue;
string MatHealth_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(1).StringCellValue;
string MatHealth_HiEHRef_ReRefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(2).StringCellValue;
string MatHealth_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(3).StringCellValue;
string MatHealth_CP_DateOfFirstANC = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(5).StringCellValue;
string MatHealth_CP_DateOfLastANC = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(6).StringCellValue;
string MatHealth_CP_RefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(7).StringCellValue;
string MatHealth_CP_RefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(8).StringCellValue;
string MatHealth_CP_RegisterForMomConnect = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(9).StringCellValue;
string MatHealth_CP_ReRefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(10).StringCellValue;
string MatHealth_CP_ReRefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(11).StringCellValue;
string MatHealth_CP_DateOfNextANC = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(12).StringCellValue;
string MatHealth_CP_DeliveryDate = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(13).StringCellValue;
string MatHealth_CP_IntendBreastFeed = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(14).StringCellValue;
string MatHealth_CP_IntendFormula = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(15).StringCellValue;
string MatHealth_PP_AreYouPregnant = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(17).StringCellValue;
string MatHealth_PP_DonePregnancyTest = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(18).StringCellValue;
string MatHealth_PP_Result = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(19).StringCellValue;
string MatHealth_PP_RefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(20).StringCellValue;
string MatHealth_PP_RefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(21).StringCellValue;
#endregion
#region ChildHealth
//HiEHRef = HiEH Referral
//NB = New Born
//CD = Child Development
//SDR = Social Development Referral
//CSDR = Current Social Development Referral
string ChildHealthDateOfVisit = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(0).StringCellValue;
string ChildHealth_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(1).StringCellValue;
string ChildHealth_HiEHRef_ReRefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(2).StringCellValue;
string ChildHealth_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(3).StringCellValue;
string ChildHealth_NB_ChildWithRHC = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(5).StringCellValue;
string ChildHealth_NB_RefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(6).StringCellValue;
string ChildHealth_NB_RefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(7).StringCellValue;
string ChildHealth_NB_MotherHIVPos = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(8).StringCellValue;
string ChildHealth_NB_ChildBreastfed = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(9).StringCellValue;
string ChildHealth_NB_BreastfedHowLong = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(10).StringCellValue;
string ChildHealth_NB_OnNevirapine = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(11).StringCellValue;
string ChildHealth_NB_RefToClininc2 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(12).StringCellValue;
string ChildHealth_NB_RefNum2 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(13).StringCellValue;
string ChildHealth_NB_PCRDone = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(14).StringCellValue;
string ChildHealth_NB_RefToClinic3 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(15).StringCellValue;
string ChildHealth_NB_RefNum3 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(16).StringCellValue;
string ChildHealth_NB_ImmuneUpToDate = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(17).StringCellValue;
string ChildHealth_NB_ImmuneOutstanding = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(18).StringCellValue;
string ChildHealth_NB_VITAandWormsGiven = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(19).StringCellValue;
string ChildHealth_NB_RefToClinic4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(20).StringCellValue;
string ChildHealth_NB_RefNum4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(21).StringCellValue;
string ChildHealth_CD_WalkRight = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(23).StringCellValue;
string ChildHealth_CD_TalkRight = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(24).StringCellValue;
string ChildHealth_CD_RefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(25).StringCellValue;
string ChildHealth_CD_RefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(26).StringCellValue;
string ChildHealth_SDR_ChildAssisted = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(28).StringCellValue;
string ChildHealth_SDR_ReRefToSD = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(29).StringCellValue;
string ChildHealth_SDR_ReRefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(30).StringCellValue;
string ChildHealth_CSDR_ListOfConcerns = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(31).StringCellValue;
string ChildHealth_CSDR_RefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(32).StringCellValue;
string ChildHealth_CSDR_RefToSD = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(33).StringCellValue;
string ChildHealth_CSDR_RefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(34).StringCellValue;
#endregion
#region Other
//HiEHRef = HiEH Referral
//OC = Other Condition
string OCDateOfVisit = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(0).StringCellValue;
string OC_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(1).StringCellValue;
string OC_HiEHRef_ReRefToClinic = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(2).StringCellValue;
string OC_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(3).StringCellValue;
string OC_ConditionThat = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(5).StringCellValue;
string OC_RefToClinic = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(6).StringCellValue;
string OC_RefNum = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(7).StringCellValue;
#endregion
// Queries here
}
}
catch (Exception ex)
{
Success = false;
ErrorList.Add(ex.Message);
}
}
if (Success == false)
{
System.Windows.MessageBox.Show("Some errors occurred. Please check the log file for a list of errors.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information);
File.WriteAllLines(Directory.GetCurrentDirectory() + "ImportErrorLog.txt", ErrorList);
System.Diagnostics.Process.Start(Directory.GetCurrentDirectory() + "ImportErrorLog.txt");
}
else
System.Windows.MessageBox.Show(Files.Length.ToString() + " file(s) have been successfully imported.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information);
return Success;
}
public void TestMergingOverlappingCols_OVERLAPS_2_MINOR()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
CT_Col col = cols.AddNewCol();
col.min=(2 + 1);
col.max=(4 + 1);
col.width=(20);
col.customWidth=(true);
sheet.GroupColumn((short)1, (short)3);
cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "testMergingOverlappingCols_OVERLAPS_2_MINOR/cols:" + cols);
Assert.AreEqual(1, cols.GetColArray(0).outlineLevel);
Assert.AreEqual(2, cols.GetColArray(0).min); // 1 based
Assert.AreEqual(2, cols.GetColArray(0).max); // 1 based
Assert.AreEqual(false, cols.GetColArray(0).customWidth);
Assert.AreEqual(1, cols.GetColArray(1).outlineLevel);
Assert.AreEqual(3, cols.GetColArray(1).min); // 1 based
Assert.AreEqual(4, cols.GetColArray(1).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(1).customWidth);
Assert.AreEqual(0, cols.GetColArray(2).outlineLevel);
Assert.AreEqual(5, cols.GetColArray(2).min); // 1 based
Assert.AreEqual(5, cols.GetColArray(2).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(2).customWidth);
Assert.AreEqual(3, cols.sizeOfColArray());
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testMergingOverlappingCols_OVERLAPS_2_MINOR");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 2; i <= 4; i++)
{
Assert.AreEqual(20 * 256, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
Assert.AreEqual(sheet.DefaultColumnWidth * 256, sheet.GetColumnWidth(1),"Unexpected width of column " + 1 );
}
public IList<NextWeekTask> getNextWeekTask(Stream nwstream)
{
var list = new List<NextWeekTask>();
var workbook = new XSSFWorkbook(nwstream);
var sheet = workbook.GetSheet("项目周报");
int maxrow = sheet.LastRowNum-1;
int startrow = 0,endrow=maxrow;
for (int i = 0; i < maxrow - 1; i++)
{
var currentRow = sheet.GetRow(i);
var startcellLbl=currentRow.Cells[0].ToString();
var endcellLbl=currentRow.Cells[0].ToString();
if (startcellLbl == "下周工作计划")
startrow = i;
}
for (int i = startrow+2; i < maxrow - 1; i++)
{
var currentRow = sheet.GetRow(i);
var cells = currentRow.Cells;
var task = new NextWeekTask();
if (currentRow != null)
{
foreach (var cell in cells)
{
switch (cell.ColumnIndex)
{
case 1:
task.TaskNum = this.CellValueTyper(cell).ToString();
break;
case 2:
task.TaskName = this.CellValueTyper(cell).ToString();
break;
case 3:
task.ProjectNum = this.CellValueTyper(cell).ToString();
break;
case 4:
task.TaskType = this.CellValueTyper(cell).ToString();
break;
case 5:
task.InPlan = this.CellValueTyper(cell).ToString();
break;
case 6:
task.Description = this.CellValueTyper(cell).ToString();
break;
case 7:
task.Percent = this.CellValueTyper(cell).ToString();
break;
case 8:
try
{
task.StartTime = cell.DateCellValue.ToString("yyyy-MM-dd");
}
catch
{
task.StartTime = string.Empty;
}
break;
case 9:
try
{
task.EndTime = cell.DateCellValue.ToString("yyyy-MM-dd"); ;
}
catch {
task.EndTime = string.Empty;
}
break;
case 10:
try
{
task.ManDay = int.Parse(this.CellValueTyper(cell).ToString());
}
catch
{
task.ManDay = 0;
}
break;
case 11:
task.Employee = this.CellValueTyper(cell).ToString();
break;
case 12:
task.PlanEmployee = this.CellValueTyper(cell).ToString();
break;
case 13:
task.Result = this.CellValueTyper(cell).ToString();
break;
case 14:
task.Commit = this.CellValueTyper(cell).ToString();
break;
default:
break;
}
}
list.Add(task);
}
}
return list;
}
public ActionResult ImportExcel(MProjectImportExcelStub model)
{
if (ModelState.IsValid)
{
XSSFSheet sheet; int colCount; int rowCount; int startRow = 3; int colNum; int id;
List<string> messageList = new List<string>();
string currentFilePathInServer = Server.MapPath(model.ExcelFilePath);
XSSFWorkbook book;
try
{
using (FileStream file = new FileStream(currentFilePathInServer, FileMode.Open, FileAccess.Read))
{
book = new XSSFWorkbook(file);
}
sheet = (XSSFSheet)book.GetSheet("Data");
rowCount = sheet.LastRowNum;
colNum = 0;
id = 0;
bool allowSave = true;
for (int row = startRow; row <= rowCount; row++)
{
colNum = 0;
var dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
if (dt != null)
{
allowSave = true;
sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
if (sheet.GetRow(row).GetCell(colNum).StringCellValue != "")
{
m_project insertData = new m_project();
m_project filledData = this.FillDataFromExcel(insertData, sheet, row, colNum,ref messageList, ref allowSave);
if (allowSave == true)
{
RepoMProject.Save(filledData);
}
}
}
}
}
catch (Exception e)
{
messageList.Add("An error has occurred. Please contact administrator.");
}
ViewBag.MessageList = messageList;
return View("ImportExcelForm", model);
//return ret;
}
else
{
ViewBag.MessageList = null;
return View("ImportExcelForm", model);
}
}
public static bool Import(string TargetFolder)
{
bool Success = true;
string[] Files = Directory.GetFiles(TargetFolder);
List<string> ErrorList = new List<string>();
foreach (string CurrentFile in Files)
{
try
{
XSSFWorkbook MyWorkbook = null;
using (FileStream file = new FileStream(CurrentFile, FileMode.Open, FileAccess.Read))
{
MyWorkbook = new XSSFWorkbook(file);
// Read file values here
#region Biographical
//Notes: Did not make provisions for multiple Clinics, Schools or Grades - Client must confirm if it is necessary
string BioChowName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(0).StringCellValue;
string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(1).StringCellValue;
string BioDateOfScreen = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(2).StringCellValue;
string BioHeadOfHousehold = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(3).StringCellValue;
string BioName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(4).StringCellValue;
string BioSurname = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(5).StringCellValue;
string BioGPSLat = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(6).StringCellValue;
string BioGPSLong = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(7).StringCellValue;
string BioIDNum = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(8).StringCellValue;
string BioClinicUsed = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(9).StringCellValue;
string BioDateOfBirth = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(10).StringCellValue;
string BioMale = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(11).StringCellValue;
string BioFemale = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(12).StringCellValue;
string BioAttendingSchool = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(13).StringCellValue;
string BioSchoolName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(14).StringCellValue;
string BioGrade = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(15).StringCellValue;
#endregion
#region Environmental
//Notes: Did not make provisions for multiple Clinics, - Client must confirm if it is necessary
//Notes" Made provision for 5 huts in total
//Notes" Made provision for 2 water Supplies
string EnNoOfPeople = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(1).StringCellValue;
string EnNoLiveAway = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(2).StringCellValue;
string EnListWhere0 = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(3).StringCellValue;
string EnListWhere1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(3).StringCellValue;
string EnListWhere2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(3).StringCellValue;
string EnListWhere3 = MyWorkbook.GetSheet("Environmental").GetRow(7).GetCell(3).StringCellValue;
string EnListWhere4 = MyWorkbook.GetSheet("Environmental").GetRow(8).GetCell(3).StringCellValue;
string EnFamilyLastVisit0 = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(4).StringCellValue;
string EnFamilyLastVisit1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(4).StringCellValue;
string EnFamilyLastVisit2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(4).StringCellValue;
string EnFamilyLastVisit3 = MyWorkbook.GetSheet("Environmental").GetRow(7).GetCell(4).StringCellValue;
string EnFamilyLastVisit4 = MyWorkbook.GetSheet("Environmental").GetRow(8).GetCell(4).StringCellValue;
string EnWhichClinic = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(5).StringCellValue;
string EnMainHutStructure = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(6).StringCellValue;
string EnMainHutTypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(7).StringCellValue;
string EnMainHutVentilation = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(8).StringCellValue;
string EnMainHutTotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(9).StringCellValue;
string EnHut2Structure = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(6).StringCellValue;
string EnHut2TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(7).StringCellValue;
string EnHut2Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(8).StringCellValue;
string EnHut2TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(9).StringCellValue;
string EnHut3Structure = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(6).StringCellValue;
string EnHut3TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(7).StringCellValue;
string EnHut3Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(8).StringCellValue;
string EnHut3TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(9).StringCellValue;
string EnHut4Structure = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(6).StringCellValue;
string EnHut4TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(7).StringCellValue;
string EnHut4Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(8).StringCellValue;
string EnHut4TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(9).StringCellValue;
string EnHut5Structure = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(6).StringCellValue;
string EnHut5TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(7).StringCellValue;
string EnHut5Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(8).StringCellValue;
string EnHut5TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(9).StringCellValue;
string EnNoSleepingInOneRoom = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(10).StringCellValue;
string EnNoOfStructures = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(11).StringCellValue;
string EnRainWaterCollection = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(12).StringCellValue;
string EnWaterSupply = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(13).StringCellValue;
string EnWaterSupply1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(13).StringCellValue;
string EnWalkingDistanceWater = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(14).StringCellValue;
string EnTreatWater = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(15).StringCellValue;
string EnHutElectricity = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(16).StringCellValue;
string EnFridge = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(17).StringCellValue;
string EnUseForCooking = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(18).StringCellValue;
string EnUseForCooking1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(18).StringCellValue;
string EnUseForCooking2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(18).StringCellValue;
string EnTypeToilet = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(19).StringCellValue;
string EnDisposeWaste = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(20).StringCellValue;
string EnDisposeWaste1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(20).StringCellValue;
string EnSourceIncomeHousehold = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(21).StringCellValue;
string EnFoodParcel = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(22).StringCellValue;
#endregion
#region General
string GenWeight = MyWorkbook.GetSheet("General").GetRow(4).GetCell(1).StringCellValue;
string GenHeight = MyWorkbook.GetSheet("General").GetRow(4).GetCell(2).StringCellValue;
string GenBMI = MyWorkbook.GetSheet("General").GetRow(4).GetCell(3).StringCellValue;
string GenCurrentOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(5).StringCellValue;
string GenCurrentNotOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(6).StringCellValue;
string GenCurrentHPT = MyWorkbook.GetSheet("General").GetRow(4).GetCell(8).StringCellValue;
string GenCurrentHPTListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(9).StringCellValue;
string GenCurrentHPTListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(9).StringCellValue;
string GenCurrentHPTListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(9).StringCellValue;
string GenCurrentHPTListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(9).StringCellValue;
string GenCurrentHPTListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(9).StringCellValue;
string GenCurrentHPTStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(10).StringCellValue;
string GenCurrentHPTDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(11).StringCellValue;
string GenCurrentHPTReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(12).StringCellValue;
string GenCurrentHPTReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(13).StringCellValue;
string GenCurrentDiabetes = MyWorkbook.GetSheet("General").GetRow(4).GetCell(15).StringCellValue;
string GenCurrentDiabetesListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(16).StringCellValue;
string GenCurrentDiabetesListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(16).StringCellValue;
string GenCurrentDiabetesListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(16).StringCellValue;
string GenCurrentDiabetesListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(16).StringCellValue;
string GenCurrentDiabetesListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(16).StringCellValue;
string GenCurrentDiabetesStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(17).StringCellValue;
string GenCurrentDiabetesDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(18).StringCellValue;
string GenCurrentDiabetesReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(19).StringCellValue;
string GenCurrentDiabetesReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(20).StringCellValue;
string GenCurrentEpilepsy = MyWorkbook.GetSheet("General").GetRow(4).GetCell(22).StringCellValue;
string GenCurrentEpilepsyListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(23).StringCellValue;
string GenCurrentEpilepsyListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(23).StringCellValue;
string GenCurrentEpilepsyListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(23).StringCellValue;
string GenCurrentEpilepsyListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(23).StringCellValue;
string GenCurrentEpilepsyListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(23).StringCellValue;
string GenCurrentEpilepsyStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(24).StringCellValue;
string GenCurrentEpilepsyDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(25).StringCellValue;
string GenCurrentEpilepsyReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(26).StringCellValue;
string GenCurrentEpilepsyReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(27).StringCellValue;
string GenCurrentAsthma = MyWorkbook.GetSheet("General").GetRow(4).GetCell(29).StringCellValue;
string GenCurrentAsthmaListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(30).StringCellValue;
string GenCurrentAsthmaListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(30).StringCellValue;
string GenCurrentAsthmaListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(30).StringCellValue;
string GenCurrentAsthmaListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(30).StringCellValue;
string GenCurrentAsthmaListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(30).StringCellValue;
string GenCurrentAsthmaStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(31).StringCellValue;
string GenCurrentAsthmaDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(32).StringCellValue;
string GenCurrentAsthmaReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(33).StringCellValue;
string GenCurrentAsthmaReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(34).StringCellValue;
string GenCurrentOther = MyWorkbook.GetSheet("General").GetRow(4).GetCell(36).StringCellValue;
string GenCurrentOtherListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(37).StringCellValue;
string GenCurrentOtherListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(37).StringCellValue;
string GenCurrentOtherListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(37).StringCellValue;
string GenCurrentOtherListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(37).StringCellValue;
string GenCurrentOtherListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(37).StringCellValue;
string GenCurrentOtherStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(38).StringCellValue;
string GenCurrentOtherDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(39).StringCellValue;
string GenCurrentOtherReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(40).StringCellValue;
string GenCurrentOtherReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(41).StringCellValue;
string GenBPOnMedsSystolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(43).StringCellValue;
string GenBPOnMedsDiatolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(44).StringCellValue;
string GenBPNotOnMedsSystolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(45).StringCellValue;
string GenBPNotOnMedsDiatolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(46).StringCellValue;
string GenBPReferCHOW = MyWorkbook.GetSheet("General").GetRow(4).GetCell(47).StringCellValue;
string GenBPReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(48).StringCellValue;
string GenBPReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(49).StringCellValue;
string GenBSOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(51).StringCellValue;
string GenBSNotOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(52).StringCellValue;
string GenBSReferChow = MyWorkbook.GetSheet("General").GetRow(4).GetCell(53).StringCellValue;
string GenBSReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(54).StringCellValue;
string GenBSReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(55).StringCellValue;
string GenEPFitsMonth = MyWorkbook.GetSheet("General").GetRow(4).GetCell(57).StringCellValue;
string GenEPReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(58).StringCellValue;
string GenEPReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(59).StringCellValue;
string GenHIVPosStatus = MyWorkbook.GetSheet("General").GetRow(4).GetCell(61).StringCellValue;
string GenHIVNegStatus = MyWorkbook.GetSheet("General").GetRow(4).GetCell(62).StringCellValue;
string GenHIVTestDone = MyWorkbook.GetSheet("General").GetRow(4).GetCell(63).StringCellValue;
string GenHIVResult = MyWorkbook.GetSheet("General").GetRow(4).GetCell(64).StringCellValue;
string GenHIVReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(65).StringCellValue;
string GenHIVReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(66).StringCellValue;
string GenPregCurrently = MyWorkbook.GetSheet("General").GetRow(4).GetCell(68).StringCellValue;
string GenPregPossible = MyWorkbook.GetSheet("General").GetRow(4).GetCell(69).StringCellValue;
string GenPregTestDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(70).StringCellValue;
string GenPregResult = MyWorkbook.GetSheet("General").GetRow(4).GetCell(71).StringCellValue;
string GenPregReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(72).StringCellValue;
string GenPregReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(73).StringCellValue;
string GenTBCurrentHave = MyWorkbook.GetSheet("General").GetRow(4).GetCell(75).StringCellValue;
string GenTBCurrentMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(76).StringCellValue;
string GenTBCurrentMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(76).StringCellValue;
string GenTBCurrentMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(76).StringCellValue;
string GenTBCurrentMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(76).StringCellValue;
string GenTBCurrentMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(76).StringCellValue;
string GenTBCurrentDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(77).StringCellValue;
string GenTBSymtomWeightLoss = MyWorkbook.GetSheet("General").GetRow(4).GetCell(78).StringCellValue;
string GenTBSymtomSweat = MyWorkbook.GetSheet("General").GetRow(4).GetCell(79).StringCellValue;
string GenTBSymtomFeaver = MyWorkbook.GetSheet("General").GetRow(4).GetCell(80).StringCellValue;
string GenTBSymtomCough = MyWorkbook.GetSheet("General").GetRow(4).GetCell(81).StringCellValue;
string GenTBSymtomApetite = MyWorkbook.GetSheet("General").GetRow(4).GetCell(82).StringCellValue;
string GenTBSymtomReferClininc = MyWorkbook.GetSheet("General").GetRow(4).GetCell(83).StringCellValue;
string GenTBSymtomReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(84).StringCellValue;
string GenTBTraceHousholdOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(85).StringCellValue;
string GenTBTraceReferClininc = MyWorkbook.GetSheet("General").GetRow(4).GetCell(86).StringCellValue;
string GenTBTraceReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(87).StringCellValue;
string GenOtherBloodUrine = MyWorkbook.GetSheet("General").GetRow(4).GetCell(89).StringCellValue;
string GenOtherReferClinic1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(90).StringCellValue;
string GenOtherReferNo1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(91).StringCellValue;
string GenOtherSmoking = MyWorkbook.GetSheet("General").GetRow(4).GetCell(93).StringCellValue;
string GenOtherAlcohol = MyWorkbook.GetSheet("General").GetRow(4).GetCell(94).StringCellValue;
string GenOtherDiarrhoea = MyWorkbook.GetSheet("General").GetRow(4).GetCell(96).StringCellValue;
string GenOtherReferClinic2 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(97).StringCellValue;
string GenOtherReferNo2 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(98).StringCellValue;
string GenOtherInitiationSchool = MyWorkbook.GetSheet("General").GetRow(4).GetCell(100).StringCellValue;
string GenOtherLegCramps = MyWorkbook.GetSheet("General").GetRow(4).GetCell(102).StringCellValue;
string GenOtherLegNumb = MyWorkbook.GetSheet("General").GetRow(4).GetCell(103).StringCellValue;
string GenOtherFootUlcer = MyWorkbook.GetSheet("General").GetRow(4).GetCell(104).StringCellValue;
string GenOtherReferClinic3 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(105).StringCellValue;
string GenOtherReferNo3 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(106).StringCellValue;
string GenElderAmputation = MyWorkbook.GetSheet("General").GetRow(4).GetCell(108).StringCellValue;
string GenElderVision = MyWorkbook.GetSheet("General").GetRow(4).GetCell(109).StringCellValue;
string GenElderBedridden = MyWorkbook.GetSheet("General").GetRow(4).GetCell(110).StringCellValue;
string GenElderMovingAid = MyWorkbook.GetSheet("General").GetRow(4).GetCell(111).StringCellValue;
string GenElderWash = MyWorkbook.GetSheet("General").GetRow(4).GetCell(112).StringCellValue;
string GenElderFeed = MyWorkbook.GetSheet("General").GetRow(4).GetCell(113).StringCellValue;
string GenElderDress = MyWorkbook.GetSheet("General").GetRow(4).GetCell(114).StringCellValue;
string GenElderReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(115).StringCellValue;
string GenElderReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(116).StringCellValue;
string GenFamilyPlan = MyWorkbook.GetSheet("General").GetRow(4).GetCell(118).StringCellValue;
#endregion
#region Hypertention
string HypYear = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(1).StringCellValue;
string HypHeadache = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(3).StringCellValue;
string HypVision = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(4).StringCellValue;
string HypShortBreath = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(5).StringCellValue;
string HypConfusion = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(6).StringCellValue;
string HypChestPain = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(7).StringCellValue;
string HypReferClinic = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(8).StringCellValue;
string HypReferNo = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(9).StringCellValue;
string HypHadStroke = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(11).StringCellValue;
string HypHadStrokeYear = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(12).StringCellValue;
string HypFamilyOnMeds = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(13).StringCellValue;
string HypFamilyStroke = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(14).StringCellValue;
#endregion
#region Diabetes
string DYear = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(1).StringCellValue;
string DThirsty = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(3).StringCellValue;
string DWeightloss = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(4).StringCellValue;
string DVision = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(5).StringCellValue;
string DUrinate = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(6).StringCellValue;
string DNausea = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(7).StringCellValue;
string DFoot = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(8).StringCellValue;
string DReferClinic = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(9).StringCellValue;
string DReferNo = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(10).StringCellValue;
string DFamilyMember = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(12).StringCellValue;
#endregion
#region HIV
string HIVYear = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(1).StringCellValue;
string HIVOnMeds = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(2).StringCellValue;
string HIVListMeds1 = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(3).StringCellValue;
string HIVListMeds2 = MyWorkbook.GetSheet("HIV").GetRow(3).GetCell(3).StringCellValue;
string HIVListMeds3 = MyWorkbook.GetSheet("HIV").GetRow(4).GetCell(3).StringCellValue;
string HIVListMeds4 = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(3).StringCellValue;
string HIVListMeds5 = MyWorkbook.GetSheet("HIV").GetRow(6).GetCell(3).StringCellValue;
string HIVAdherence = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(4).StringCellValue;
string HIVReferClinic = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(5).StringCellValue;
string HIVReferNo = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(6).StringCellValue;
string HIVARVNo = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(7).StringCellValue;
#endregion
#region Maternal Health
string MHPregnantBefore = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(1).StringCellValue;
string MHNoPregnant = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(2).StringCellValue;
string MHNOSuccessful = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(3).StringCellValue;
string MHWhereDeliveredLast = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(4).StringCellValue;
string MHCaesarian = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(5).StringCellValue;
string MHBabyUnder25 = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(6).StringCellValue;
string MHChildrenDied1 = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(8).StringCellValue;
string MHChildrenDied15 = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(9).StringCellValue;
string MHPAPSmear = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(10).StringCellValue;
string MHBloodResult = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(11).StringCellValue;
string MHFirstANCDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(13).StringCellValue;
string MHLastANCDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(14).StringCellValue;
string MHReferClinic = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(15).StringCellValue;
string MHReferNo = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(16).StringCellValue;
string MHNextANCDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(17).StringCellValue;
string MHExpectedDeliverDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(18).StringCellValue;
string MHBreastfeed = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(19).StringCellValue;
string MHFormula = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(20).StringCellValue;
string MHRegisteredMomConnect = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(21).StringCellValue;
#endregion
#region Child Health
string CHNameMother = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(1).StringCellValue;
string CHChildRTHC = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(3).StringCellValue;
string CHReferClinic1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(4).StringCellValue;
string CHReferNo1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(5).StringCellValue;
string CHMotherHIV = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(7).StringCellValue;
string CHChildBreastfeed = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(8).StringCellValue;
string CHHowLong = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(9).StringCellValue;
string CHChildOnNevirapine = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(10).StringCellValue;
string CHPCR = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(11).StringCellValue;
string CHPCRResult = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(12).StringCellValue;
string CHReferClininc2 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(13).StringCellValue;
string CHReferNo2 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(14).StringCellValue;
string CHImmunisationUpToDate = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(15).StringCellValue;
string CHImmunisationOutstanding1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(16).StringCellValue;
string CHImmunisationOutstanding2 = MyWorkbook.GetSheet("Child health").GetRow(3).GetCell(16).StringCellValue;
string CHImmunisationOutstanding3 = MyWorkbook.GetSheet("Child health").GetRow(4).GetCell(16).StringCellValue;
string CHImmunisationOutstanding4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(16).StringCellValue;
string CHImmunisationOutstanding5 = MyWorkbook.GetSheet("Child health").GetRow(6).GetCell(16).StringCellValue;
string CHReferClinic3 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(17).StringCellValue;
string CHReferNo3 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(18).StringCellValue;
string CHMedsGiven = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(19).StringCellValue;
string CHWalkAppropriate = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(21).StringCellValue;
string CHTalkAppropriate = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(22).StringCellValue;
string CHChildConcerns1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(24).StringCellValue;
string CHChildConcerns2 = MyWorkbook.GetSheet("Child health").GetRow(3).GetCell(24).StringCellValue;
string CHChildConcerns3 = MyWorkbook.GetSheet("Child health").GetRow(4).GetCell(24).StringCellValue;
string CHChildConcerns4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(24).StringCellValue;
string CHChildConcerns5 = MyWorkbook.GetSheet("Child health").GetRow(6).GetCell(24).StringCellValue;
string CHReferClinic4 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(25).StringCellValue;
string CHReferOVC = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(26).StringCellValue;
string CHReferNo4 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(27).StringCellValue;
#endregion
#region Other
string OCondition1 = MyWorkbook.GetSheet("Other").GetRow(2).GetCell(1).StringCellValue;
string OReferClinic1 = MyWorkbook.GetSheet("Other").GetRow(2).GetCell(2).StringCellValue;
string OReferNo1 = MyWorkbook.GetSheet("Other").GetRow(2).GetCell(2).StringCellValue;
string OCondition2 = MyWorkbook.GetSheet("Other").GetRow(3).GetCell(1).StringCellValue;
string OReferClinic2 = MyWorkbook.GetSheet("Other").GetRow(3).GetCell(2).StringCellValue;
string OReferNo2 = MyWorkbook.GetSheet("Other").GetRow(3).GetCell(2).StringCellValue;
string OCondition3 = MyWorkbook.GetSheet("Other").GetRow(4).GetCell(1).StringCellValue;
string OReferClinic3 = MyWorkbook.GetSheet("Other").GetRow(4).GetCell(2).StringCellValue;
string OReferNo3 = MyWorkbook.GetSheet("Other").GetRow(4).GetCell(2).StringCellValue;
string OCondition4 = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(1).StringCellValue;
string OReferClinic4 = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(2).StringCellValue;
string OReferNo4 = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(2).StringCellValue;
string OCondition5 = MyWorkbook.GetSheet("Other").GetRow(6).GetCell(1).StringCellValue;
string OReferClinic5 = MyWorkbook.GetSheet("Other").GetRow(6).GetCell(2).StringCellValue;
string OReferNo5 = MyWorkbook.GetSheet("Other").GetRow(6).GetCell(2).StringCellValue;
#endregion
// Queries here
string ScreeningID = Utilities.GenerateScreeningID(BioName, BioSurname);
#region Biographical
SqlConnection tempConnectionBio = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionBio.Open();
SqlCommand tempCommand = new SqlCommand("InsertBiographical", tempConnectionBio);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", BioChowName);
tempCommand.Parameters.AddWithValue("@", BioUniqueID);
tempCommand.Parameters.AddWithValue("@", BioDateOfScreen);
tempCommand.Parameters.AddWithValue("@", BioHeadOfHousehold);
tempCommand.Parameters.AddWithValue("@", BioName);
tempCommand.Parameters.AddWithValue("@", BioSurname);
tempCommand.Parameters.AddWithValue("@", BioGPSLat);
tempCommand.Parameters.AddWithValue("@", BioGPSLong);
tempCommand.Parameters.AddWithValue("@", BioIDNum);
tempCommand.Parameters.AddWithValue("@", BioClinicUsed);
tempCommand.Parameters.AddWithValue("@", BioDateOfBirth);
tempCommand.Parameters.AddWithValue("@", BioMale);
tempCommand.Parameters.AddWithValue("@", BioFemale);
tempCommand.Parameters.AddWithValue("@", BioAttendingSchool);
tempCommand.Parameters.AddWithValue("@", BioSchoolName);
tempCommand.Parameters.AddWithValue("@", BioGrade);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionBio.Close();
}
#endregion
#region Environmental
SqlConnection tempConnectionEnv = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionEnv.Open();
SqlCommand tempCommand = new SqlCommand("InsertEnvironmental", tempConnectionEnv);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", EnNoOfPeople);
tempCommand.Parameters.AddWithValue("@", EnNoLiveAway);
tempCommand.Parameters.AddWithValue("@", EnListWhere0);
tempCommand.Parameters.AddWithValue("@", EnListWhere1);
tempCommand.Parameters.AddWithValue("@", EnListWhere2);
tempCommand.Parameters.AddWithValue("@", EnListWhere3);
tempCommand.Parameters.AddWithValue("@", EnListWhere4);
tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit0);
tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit1);
tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit2);
tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit3);
tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit4);
tempCommand.Parameters.AddWithValue("@", EnWhichClinic);
tempCommand.Parameters.AddWithValue("@", EnMainHutStructure);
tempCommand.Parameters.AddWithValue("@", EnMainHutTypeRoof);
tempCommand.Parameters.AddWithValue("@", EnMainHutVentilation);
tempCommand.Parameters.AddWithValue("@", EnMainHutTotalRooms);
tempCommand.Parameters.AddWithValue("@", EnHut2Structure);
tempCommand.Parameters.AddWithValue("@", EnHut2TypeRoof);
tempCommand.Parameters.AddWithValue("@", EnHut2Ventilation);
tempCommand.Parameters.AddWithValue("@", EnHut2TotalRooms);
tempCommand.Parameters.AddWithValue("@", EnHut3Structure);
tempCommand.Parameters.AddWithValue("@", EnHut3TypeRoof);
tempCommand.Parameters.AddWithValue("@", EnHut3Ventilation);
tempCommand.Parameters.AddWithValue("@", EnHut3TotalRooms);
tempCommand.Parameters.AddWithValue("@", EnHut4Structure);
tempCommand.Parameters.AddWithValue("@", EnHut4TypeRoof);
tempCommand.Parameters.AddWithValue("@", EnHut4Ventilation);
tempCommand.Parameters.AddWithValue("@", EnHut4TotalRooms);
tempCommand.Parameters.AddWithValue("@", EnHut5Structure);
tempCommand.Parameters.AddWithValue("@", EnHut5TypeRoof);
tempCommand.Parameters.AddWithValue("@", EnHut5Ventilation);
tempCommand.Parameters.AddWithValue("@", EnHut5TotalRooms);
tempCommand.Parameters.AddWithValue("@", EnNoSleepingInOneRoom);
tempCommand.Parameters.AddWithValue("@", EnNoOfStructures);
tempCommand.Parameters.AddWithValue("@", EnRainWaterCollection);
tempCommand.Parameters.AddWithValue("@", EnWaterSupply);
tempCommand.Parameters.AddWithValue("@", EnWaterSupply1);
tempCommand.Parameters.AddWithValue("@", EnWalkingDistanceWater);
tempCommand.Parameters.AddWithValue("@", EnTreatWater);
tempCommand.Parameters.AddWithValue("@", EnHutElectricity);
tempCommand.Parameters.AddWithValue("@", EnFridge);
tempCommand.Parameters.AddWithValue("@", EnUseForCooking);
tempCommand.Parameters.AddWithValue("@", EnUseForCooking1);
tempCommand.Parameters.AddWithValue("@", EnUseForCooking2);
tempCommand.Parameters.AddWithValue("@", EnTypeToilet);
tempCommand.Parameters.AddWithValue("@", EnDisposeWaste);
tempCommand.Parameters.AddWithValue("@", EnDisposeWaste1);
tempCommand.Parameters.AddWithValue("@", EnSourceIncomeHousehold);
tempCommand.Parameters.AddWithValue("@", EnFoodParcel);
tempCommand.ExecuteScalar();
}
catch { }
finally
{
tempConnectionEnv.Close();
}
#endregion
#region General
SqlConnection tempConnectionGen = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionGen.Open();
SqlCommand tempCommand = new SqlCommand("InsertGeneral", tempConnectionGen);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", GenWeight);
tempCommand.Parameters.AddWithValue("@", GenHeight);
tempCommand.Parameters.AddWithValue("@", GenBMI);
tempCommand.Parameters.AddWithValue("@", GenCurrentOnMeds);
tempCommand.Parameters.AddWithValue("@", GenCurrentNotOnMeds);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPT);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds1);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds2);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds3);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds4);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds5);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTStartDate);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTDefaulting);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTReferClinic);
tempCommand.Parameters.AddWithValue("@", GenCurrentHPTReferNo);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetes);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds1);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds2);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds3);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds4);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds5);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesStartDate);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesDefaulting);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesReferClinic);
tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesReferNo);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsy);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds1);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds2);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds3);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds4);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds5);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyStartDate);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyDefaulting);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyReferClinic);
tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyReferNo);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthma);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds1);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds2);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds3);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds4);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds5);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaStartDate);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaDefaulting);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaReferClinic);
tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaReferNo);
tempCommand.Parameters.AddWithValue("@", GenCurrentOther);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds1);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds2);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds3);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds4);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds5);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherStartDate);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherDefaulting);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherReferClinic);
tempCommand.Parameters.AddWithValue("@", GenCurrentOtherReferNo);
tempCommand.Parameters.AddWithValue("@", GenBPOnMedsSystolic);
tempCommand.Parameters.AddWithValue("@", GenBPOnMedsDiatolic);
tempCommand.Parameters.AddWithValue("@", GenBPNotOnMedsSystolic);
tempCommand.Parameters.AddWithValue("@", GenBPNotOnMedsDiatolic);
tempCommand.Parameters.AddWithValue("@", GenBPReferCHOW);
tempCommand.Parameters.AddWithValue("@", GenBPReferClinic);
tempCommand.Parameters.AddWithValue("@", GenBPReferNo);
tempCommand.Parameters.AddWithValue("@", GenBSOnMeds);
tempCommand.Parameters.AddWithValue("@", GenBSNotOnMeds);
tempCommand.Parameters.AddWithValue("@", GenBSReferChow);
tempCommand.Parameters.AddWithValue("@", GenBSReferClinic);
tempCommand.Parameters.AddWithValue("@", GenBSReferNo);
tempCommand.Parameters.AddWithValue("@", GenEPFitsMonth);
tempCommand.Parameters.AddWithValue("@", GenEPReferClinic);
tempCommand.Parameters.AddWithValue("@", GenEPReferNo);
tempCommand.Parameters.AddWithValue("@", GenHIVPosStatus);
tempCommand.Parameters.AddWithValue("@", GenHIVNegStatus);
tempCommand.Parameters.AddWithValue("@", GenHIVTestDone);
tempCommand.Parameters.AddWithValue("@", GenHIVResult);
tempCommand.Parameters.AddWithValue("@", GenHIVReferClinic);
tempCommand.Parameters.AddWithValue("@", GenHIVReferNo);
tempCommand.Parameters.AddWithValue("@", GenPregCurrently);
tempCommand.Parameters.AddWithValue("@", GenPregPossible);
tempCommand.Parameters.AddWithValue("@", GenPregTestDate);
tempCommand.Parameters.AddWithValue("@", GenPregResult);
tempCommand.Parameters.AddWithValue("@", GenPregReferClinic);
tempCommand.Parameters.AddWithValue("@", GenPregReferNo);
tempCommand.Parameters.AddWithValue("@", GenTBCurrentHave);
tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds1);
tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds2);
tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds3);
tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds4);
tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds5);
tempCommand.Parameters.AddWithValue("@", GenTBCurrentDefaulting);
tempCommand.Parameters.AddWithValue("@", GenTBSymtomWeightLoss);
tempCommand.Parameters.AddWithValue("@", GenTBSymtomSweat);
tempCommand.Parameters.AddWithValue("@", GenTBSymtomFeaver);
tempCommand.Parameters.AddWithValue("@", GenTBSymtomCough);
tempCommand.Parameters.AddWithValue("@", GenTBSymtomApetite);
tempCommand.Parameters.AddWithValue("@", GenTBSymtomReferClininc);
tempCommand.Parameters.AddWithValue("@", GenTBSymtomReferNo);
tempCommand.Parameters.AddWithValue("@", GenTBTraceHousholdOnMeds);
tempCommand.Parameters.AddWithValue("@", GenTBTraceReferClininc);
tempCommand.Parameters.AddWithValue("@", GenTBTraceReferNo);
tempCommand.Parameters.AddWithValue("@", GenOtherBloodUrine);
tempCommand.Parameters.AddWithValue("@", GenOtherReferClinic1);
tempCommand.Parameters.AddWithValue("@", GenOtherReferNo1);
tempCommand.Parameters.AddWithValue("@", GenOtherSmoking);
tempCommand.Parameters.AddWithValue("@", GenOtherAlcohol);
tempCommand.Parameters.AddWithValue("@", GenOtherDiarrhoea);
tempCommand.Parameters.AddWithValue("@", GenOtherReferClinic2);
tempCommand.Parameters.AddWithValue("@", GenOtherReferNo2);
tempCommand.Parameters.AddWithValue("@", GenOtherInitiationSchool);
tempCommand.Parameters.AddWithValue("@", GenOtherLegCramps);
tempCommand.Parameters.AddWithValue("@", GenOtherLegNumb);
tempCommand.Parameters.AddWithValue("@", GenOtherFootUlcer);
tempCommand.Parameters.AddWithValue("@", GenOtherReferClinic3);
tempCommand.Parameters.AddWithValue("@", GenOtherReferNo3);
tempCommand.Parameters.AddWithValue("@", GenElderAmputation);
tempCommand.Parameters.AddWithValue("@", GenElderVision);
tempCommand.Parameters.AddWithValue("@", GenElderBedridden);
tempCommand.Parameters.AddWithValue("@", GenElderMovingAid);
tempCommand.Parameters.AddWithValue("@", GenElderWash);
tempCommand.Parameters.AddWithValue("@", GenElderFeed);
tempCommand.Parameters.AddWithValue("@", GenElderDress);
tempCommand.Parameters.AddWithValue("@", GenElderReferClinic);
tempCommand.Parameters.AddWithValue("@", GenElderReferNo);
tempCommand.Parameters.AddWithValue("@", GenFamilyPlan);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionGen.Close();
}
#endregion
#region Hypertention
SqlConnection tempConnectionHyp = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionHyp.Open();
SqlCommand tempCommand = new SqlCommand("InsertHypertention", tempConnectionHyp);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", HypYear);
tempCommand.Parameters.AddWithValue("@", HypHeadache);
tempCommand.Parameters.AddWithValue("@", HypVision);
tempCommand.Parameters.AddWithValue("@", HypShortBreath);
tempCommand.Parameters.AddWithValue("@", HypConfusion);
tempCommand.Parameters.AddWithValue("@", HypChestPain);
tempCommand.Parameters.AddWithValue("@", HypReferClinic);
tempCommand.Parameters.AddWithValue("@", HypReferNo);
tempCommand.Parameters.AddWithValue("@", HypHadStroke);
tempCommand.Parameters.AddWithValue("@", HypHadStrokeYear);
tempCommand.Parameters.AddWithValue("@", HypFamilyOnMeds);
tempCommand.Parameters.AddWithValue("@", HypFamilyStroke);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionHyp.Close();
}
#endregion
#region Diabetes
SqlConnection tempConnectionDia = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionDia.Open();
SqlCommand tempCommand = new SqlCommand("InsertDiabetes", tempConnectionDia);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", DYear);
tempCommand.Parameters.AddWithValue("@", DThirsty);
tempCommand.Parameters.AddWithValue("@", DWeightloss);
tempCommand.Parameters.AddWithValue("@", DVision);
tempCommand.Parameters.AddWithValue("@", DUrinate);
tempCommand.Parameters.AddWithValue("@", DNausea);
tempCommand.Parameters.AddWithValue("@", DFoot);
tempCommand.Parameters.AddWithValue("@", DReferClinic);
tempCommand.Parameters.AddWithValue("@", DReferNo);
tempCommand.Parameters.AddWithValue("@", DFamilyMember);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionDia.Close();
}
#endregion
#region HIV
SqlConnection tempConnectionHIV = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionHIV.Open();
SqlCommand tempCommand = new SqlCommand("InsertHIV", tempConnectionHIV);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", HIVYear);
tempCommand.Parameters.AddWithValue("@", HIVOnMeds);
tempCommand.Parameters.AddWithValue("@", HIVListMeds1);
tempCommand.Parameters.AddWithValue("@", HIVListMeds2);
tempCommand.Parameters.AddWithValue("@", HIVListMeds3);
tempCommand.Parameters.AddWithValue("@", HIVListMeds4);
tempCommand.Parameters.AddWithValue("@", HIVListMeds5);
tempCommand.Parameters.AddWithValue("@", HIVAdherence);
tempCommand.Parameters.AddWithValue("@", HIVReferClinic);
tempCommand.Parameters.AddWithValue("@", HIVReferNo);
tempCommand.Parameters.AddWithValue("@", HIVARVNo);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionHIV.Close();
}
#endregion
#region Maternal Health
SqlConnection tempConnectionMat = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionMat.Open();
SqlCommand tempCommand = new SqlCommand("InsertMaternalHealth", tempConnectionMat);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", MHPregnantBefore);
tempCommand.Parameters.AddWithValue("@", MHNoPregnant);
tempCommand.Parameters.AddWithValue("@", MHNOSuccessful);
tempCommand.Parameters.AddWithValue("@", MHWhereDeliveredLast);
tempCommand.Parameters.AddWithValue("@", MHCaesarian);
tempCommand.Parameters.AddWithValue("@", MHBabyUnder25);
tempCommand.Parameters.AddWithValue("@", MHChildrenDied1);
tempCommand.Parameters.AddWithValue("@", MHChildrenDied15);
tempCommand.Parameters.AddWithValue("@", MHPAPSmear);
tempCommand.Parameters.AddWithValue("@", MHBloodResult);
tempCommand.Parameters.AddWithValue("@", MHFirstANCDate);
tempCommand.Parameters.AddWithValue("@", MHLastANCDate);
tempCommand.Parameters.AddWithValue("@", MHReferClinic);
tempCommand.Parameters.AddWithValue("@", MHReferNo);
tempCommand.Parameters.AddWithValue("@", MHNextANCDate);
tempCommand.Parameters.AddWithValue("@", MHExpectedDeliverDate);
tempCommand.Parameters.AddWithValue("@", MHBreastfeed);
tempCommand.Parameters.AddWithValue("@", MHFormula);
tempCommand.Parameters.AddWithValue("@", MHRegisteredMomConnect);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionMat.Close();
}
#endregion
#region Child Health
SqlConnection tempConnectionChild = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionChild.Open();
SqlCommand tempCommand = new SqlCommand("InsertChildHealth", tempConnectionChild);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", CHNameMother);
tempCommand.Parameters.AddWithValue("@", CHChildRTHC);
tempCommand.Parameters.AddWithValue("@", CHReferClinic1);
tempCommand.Parameters.AddWithValue("@", CHReferNo1);
tempCommand.Parameters.AddWithValue("@", CHMotherHIV);
tempCommand.Parameters.AddWithValue("@", CHChildBreastfeed);
tempCommand.Parameters.AddWithValue("@", CHHowLong);
tempCommand.Parameters.AddWithValue("@", CHChildOnNevirapine);
tempCommand.Parameters.AddWithValue("@", CHPCR);
tempCommand.Parameters.AddWithValue("@", CHPCRResult);
tempCommand.Parameters.AddWithValue("@", CHReferClininc2);
tempCommand.Parameters.AddWithValue("@", CHReferNo2);
tempCommand.Parameters.AddWithValue("@", CHImmunisationUpToDate);
tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding1);
tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding2);
tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding3);
tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding4);
tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding5);
tempCommand.Parameters.AddWithValue("@", CHReferClinic3);
tempCommand.Parameters.AddWithValue("@", CHReferNo3);
tempCommand.Parameters.AddWithValue("@", CHMedsGiven);
tempCommand.Parameters.AddWithValue("@", CHWalkAppropriate);
tempCommand.Parameters.AddWithValue("@", CHTalkAppropriate);
tempCommand.Parameters.AddWithValue("@", CHChildConcerns1);
tempCommand.Parameters.AddWithValue("@", CHChildConcerns2);
tempCommand.Parameters.AddWithValue("@", CHChildConcerns3);
tempCommand.Parameters.AddWithValue("@", CHChildConcerns4);
tempCommand.Parameters.AddWithValue("@", CHChildConcerns5);
tempCommand.Parameters.AddWithValue("@", CHReferClinic4);
tempCommand.Parameters.AddWithValue("@", CHReferOVC);
tempCommand.Parameters.AddWithValue("@", CHReferNo4);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionChild.Close();
}
#endregion
#region Other
SqlConnection tempConnectionOther = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
tempConnectionOther.Open();
SqlCommand tempCommand = new SqlCommand("InsertBiographical", tempConnectionOther);
tempCommand.CommandType = CommandType.StoredProcedure;
tempCommand.Parameters.AddWithValue("@", OCondition1);
tempCommand.Parameters.AddWithValue("@", OReferClinic1);
tempCommand.Parameters.AddWithValue("@", OReferNo1);
tempCommand.Parameters.AddWithValue("@", OCondition2);
tempCommand.Parameters.AddWithValue("@", OReferClinic2);
tempCommand.Parameters.AddWithValue("@", OReferNo2);
tempCommand.Parameters.AddWithValue("@", OCondition3);
tempCommand.Parameters.AddWithValue("@", OReferClinic3);
tempCommand.Parameters.AddWithValue("@", OReferNo3);
tempCommand.Parameters.AddWithValue("@", OCondition4);
tempCommand.Parameters.AddWithValue("@", OReferClinic4);
tempCommand.Parameters.AddWithValue("@", OReferNo4);
tempCommand.Parameters.AddWithValue("@", OCondition5);
tempCommand.Parameters.AddWithValue("@", OReferClinic5);
tempCommand.Parameters.AddWithValue("@", OReferNo5);
tempCommand.ExecuteNonQuery();
}
catch { }
finally
{
tempConnectionOther.Close();
}
#endregion
}
}
catch(Exception ex)
{
Success = false;
ErrorList.Add(ex.Message);
}
}
if (Success == false)
{
System.Windows.MessageBox.Show("Some errors occurred. Please check the log file for a list of errors.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information);
File.WriteAllLines(Directory.GetCurrentDirectory() + "ImportErrorLog.txt", ErrorList);
System.Diagnostics.Process.Start(Directory.GetCurrentDirectory() + "ImportErrorLog.txt");
}
else
System.Windows.MessageBox.Show(Files.Length.ToString() + " file(s) have been successfully imported.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information);
return Success;
}
public IList<Task> GetTaskList(Stream stream)
{
var list = new List<Task>();
var workbook = new XSSFWorkbook(stream);
var sheet = workbook.GetSheet("个人周报");
var employeeName = sheet.GetRow(2).Cells[15].ToString();
if(employeeName==string.Empty)
employeeName = sheet.GetRow(2).Cells[16].ToString();
int maxrow = sheet.LastRowNum;
for (int i = 4; i < maxrow-1; i++)
{
var currentRow = sheet.GetRow(i);
if (currentRow != null)
{
var cells = currentRow.Cells;
var task = new Task();
task.EmpName = employeeName;
foreach (var cell in cells)
{
switch (cell.ColumnIndex) {
case 1:
task.TaskNum = this.CellValueTyper(cell).ToString();
break;
case 2:
task.TaskName = this.CellValueTyper(cell).ToString();
break;
case 3:
task.ProjectNum = this.CellValueTyper(cell).ToString();
break;
case 4:
task.TaskType = this.CellValueTyper(cell).ToString();
break;
case 5:
task.InPlan = this.CellValueTyper(cell).ToString();
break;
case 6:
task.Description = this.CellValueTyper(cell).ToString();
break;
case 7:
if (cell.CellType == CellType.Blank)
task.Mon = 0;
else
task.Mon =(double)this.CellValueTyper(cell);
break;
case 8:
if (cell.CellType == CellType.Blank)
task.Tue = 0;
else
task.Tue = (double)this.CellValueTyper(cell);
break;
case 9:
if (cell.CellType == CellType.Blank)
task.Wen = 0;
else
task.Wen = (double)this.CellValueTyper(cell);
break;
case 10:
if (cell.CellType == CellType.Blank)
task.Thr = 0;
else
task.Thr = (double)this.CellValueTyper(cell);
break;
case 11:
if (cell.CellType == CellType.Blank)
task.Fir = 0;
else
task.Fir = (double)this.CellValueTyper(cell);
break;
case 12:
if (cell.CellType == CellType.Blank)
task.San = 0;
else
task.San = (double)this.CellValueTyper(cell);
break;
case 13:
if (cell.CellType == CellType.Blank)
task.Sun = 0;
else
task.Sun = (double)this.CellValueTyper(cell);
break;
case 14:
//if (cell.CellType == CellType.Blank)
// task.Sum = 0;
//else
//task.Sum = (double)this.CellValueTyper(cell);
task.Sum = 0;
break;
case 15:
if (cell.CellType == CellType.Blank)
task.Percent = 0.0;
else
task.Percent = (double)this.CellValueTyper(cell);
break;
case 16:
task.Result = this.CellValueTyper(cell).ToString();
break;
case 17:
task.WillFinDate = this.CellValueTyper(cell).ToString();
break;
case 18:
task.WillFinMD = this.CellValueTyper(cell).ToString();
break;
case 19:
task.Advince = this.CellValueTyper(cell).ToString();
break;
default:
break;
}
}
list.Add(task);
}
}
return list;
}
public override void Initialize(DateTime Start, DateTime End, IEnumerable<Catchment> Catchments)
{
base.Initialize(Start, End, Catchments);
Dictionary<XYPoint, List<double>> Data = new Dictionary<XYPoint,List<double>>();
XSSFWorkbook hssfwb;
using (FileStream file = new FileStream(ExcelFile.FileName, FileMode.Open, FileAccess.Read))
{
hssfwb = new XSSFWorkbook(file);
}
List<IRow> DataRows = new List<IRow>();
var sheet = hssfwb.GetSheet("Ndep_Tot");
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
DataRows.Add(sheet.GetRow(row));
}
}
using (ShapeReader sr = new ShapeReader(Shapefile.FileName))
{
FirstYear = (int)DataRows.First().Cells[0].NumericCellValue;
for (int i = 0; i < sr.Data.NoOfEntries; i++)
{
int icoor = sr.Data.ReadInt(i, "i");
int jcoor = sr.Data.ReadInt(i, "j");
XYPoint point = (XYPoint)sr.ReadNext(i);
//Create the timestampseries and set unit to kg/m2/s;
var data = DataRows.Where(v => (int)v.Cells[3].NumericCellValue == icoor & (int)v.Cells[4].NumericCellValue == jcoor).OrderBy(v => (int)v.Cells[0].NumericCellValue).Select(v => v.Cells[6].NumericCellValue / (365.0 * 86400.0 * 1.0e6)).ToList();
if(data.Count()>0)
Data.Add(point, data);
}
}
foreach (var c in Catchments)
{
XYPolygon poly = null;
if (c.Geometry is XYPolygon)
poly = c.Geometry as XYPolygon;
else if (c.Geometry is MultiPartPolygon)
poly = ((MultiPartPolygon)c.Geometry).Polygons.First(); //Just use the first polygon
double LakeArea = c.Lakes.Sum(l => l.Geometry.GetArea()); //Get the area of the lakes
if (c.BigLake != null) //Add the big lake
LakeArea += c.BigLake.Geometry.GetArea();
if (poly != null)
{
var point = new XYPoint(poly.PlotPoints.First().Longitude, poly.PlotPoints.First().Latitude); //Take one point in the polygon
var closestpoint = Data.Keys.Select(p => new Tuple<XYPoint, double>(p, p.GetDistance(point))).OrderBy(s => s.Item2).First().Item1;
deposition.Add(c.ID, new List<double>(Data[closestpoint].Select(v=>v*LakeArea)));
}
}
NewMessage("Initialized");
}
public void TestSetForceFormulaRecalculation()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet 1");
// Set
sheet.ForceFormulaRecalculation = (true);
Assert.AreEqual(true, sheet.ForceFormulaRecalculation);
// calcMode="manual" is unset when forceFormulaRecalculation=true
CT_CalcPr calcPr = workbook.GetCTWorkbook().AddNewCalcPr();
calcPr.calcMode = (ST_CalcMode.manual);
sheet.ForceFormulaRecalculation=(true);
Assert.AreEqual(ST_CalcMode.auto, calcPr.calcMode);
// Check
sheet.ForceFormulaRecalculation = (false);
Assert.AreEqual(false, sheet.ForceFormulaRecalculation);
// Save, re-load, and re-check
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = (XSSFSheet)workbook.GetSheet("Sheet 1");
Assert.AreEqual(false, sheet.ForceFormulaRecalculation);
}
private string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleNumDTO> hnDTOList, List<IWEHAVE.ERP.CenterBE.Deploy.HandleQuanDTO> hqDTOList, string fileName)
{
if (hnDTOList == null || hqDTOList == null)
{
return string.Empty;
}
string url = "http://blessing.wang/ExcelModel/model.xlsx";
WebRequest request = WebRequest.Create(url);
WebResponse response = request.GetResponse();
Stream stream = response.GetResponseStream();
XSSFWorkbook workbook = new XSSFWorkbook(stream);
//导出个数
ISheet sheet = workbook.GetSheet("寄出个数");
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(1);
cell.SetCellValue(hnDTOList.Count + "个");
row = sheet.GetRow(2);
cell = row.GetCell(7);
cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
//设置单元格格式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int i = 0; i < hnDTOList.Count; i++)
{
row = sheet.CreateRow(5 + i);
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].ContractNo);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Student);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Application);
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Visa);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Author);
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].FirstFour);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Doctor);
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Cooperation);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Note);
}
style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(5 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("编制人签名:");
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue("经理审核签名：");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("总经理/分管总裁批准签字：");
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue("保持：人力资源中心");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1．《文案工作进度（量化）日报单》汇总动态记录PM-16《文案工作量化日报表》；此单必须机打，手写量化将直接作废；");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(7 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2．本报单请于16:00之前申报， □确认√确认填报项。请勿集中中延期申报");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(8 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3．高端合同（英国前四所大学、博士）需在“英国前四所大学”、“博士”项点黑");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(9 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4．国内合作合同需在“国内合作”项点黑");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
//导出量化
sheet = workbook.GetSheet("寄出量化");
row = sheet.GetRow(1);
cell = row.GetCell(1);
cell.SetCellValue(hqDTOList.Count + "个");
row = sheet.GetRow(2);
cell = row.GetCell(6);
cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int i = 0; i < hqDTOList.Count; i++)
{
row = sheet.CreateRow(5 + i);
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].ContractNo);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Student);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue("");
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].School);
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Online);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Ps);
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue("□");
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Visa);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue("□");
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].ApplicationType);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Author);
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Note);
}
style = workbook.CreateCellStyle();
font = workbook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 9;
style.SetFont(font);
row = sheet.CreateRow(5 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("编制人签名:");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核签名：");
cell.CellStyle = style;
cell = row.CreateCell(7);
cell.SetCellValue("总经理/分管总裁批准签字：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("保持：人力资源中心");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("说明：");
cell.CellStyle = style;
cell = row.CreateCell(1);
cell.SetCellValue("1.高端研究生");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("5.艺术");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("9.老学生换无条件材料翻译");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("13.有拒签史学生签证（VO）");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("17.学生签证（VO)");
cell.CellStyle = style;
row = sheet.CreateRow(7 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("2.博士");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("6.其他只申请语言学校");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("10.国内合作");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("14.探亲、访友、陪读 (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(8 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("3.非高端研究生");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("7.个人陈述+推荐信");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("11.本科预科和研究生预科/高中");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("15.学生访问 (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("4.本科");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("8.单写个人陈述（推荐信）");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("12.至尊高中");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("16.结婚、学术访问、定居、团聚、工作签证 (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(10 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1．文案工作进度（量化）日报单》汇总动态记录JX-16《文案工作量化日报表》；此单必须机打，手写量化将直接作废；");
cell.CellStyle = style;
row = sheet.CreateRow(11 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2．本报单请于16:00之前申报，□确认√确认填报项。请勿集中延期申报");
cell.CellStyle = style;
//保存为文件
string path = HttpContext.Current.Server.MapPath("/ExportTemp/");
DirectoryInfo info = new DirectoryInfo(path);
FileInfo[] files = info.GetFiles();
if (files.Length > 0)
{
for (int i = files.Length - 1; i >= 0; i--)
{
files[i].Delete();
}
}
fileName += ".xlsx";
FileStream file = File.Create(path + fileName);
workbook.Write(file);
file.Close();
stream.Close();
return "http://blessing.wang/ExportTemp/" + fileName;
}
public static List<ProductLine> GetProductsData(string fileName, string fileExtension, string sheetName)
{
ISheet sheet;
try
{
if (fileExtension.ToLower() == ".xls")
{
HSSFWorkbook hssfwb;
// Create temp XLSX file
using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
hssfwb = new HSSFWorkbook(fileStream);
}
sheet = hssfwb.GetSheet(sheetName);
}
else
{
XSSFWorkbook hssfwb;
using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
hssfwb = new XSSFWorkbook(fileStream);
}
sheet = hssfwb.GetSheet(sheetName);
}
}
finally
{
// Make sure temp file is deleted
File.Delete(fileName);
}
var productLines = new List<ProductLine>();
if(sheet != null)
{
// DataTable dt = new DataTable();
// IRow headerRow = sheet.GetRow(0);
IEnumerator rows = sheet.GetRowEnumerator();
int rowCount = sheet.LastRowNum;
IRow firstRow = null;
// try to get first row
for (int i=0; i < 10; i++)
{
if (firstRow == null)
firstRow = sheet.GetRow(i);
else
break;
}
TemplateEnum.ExcelFileTypeEnum templateType = TemplateEnum.ExcelFileTypeEnum.Type1;
int colCount = firstRow.LastCellNum;
for (int c = 0; c < colCount; c++)
{
ICell cell = firstRow.GetCell(c);
if(cell != null)
{
if (cell.ToString().Contains("Код фабрики"))
{
templateType = TemplateEnum.ExcelFileTypeEnum.Type1;
break;
}
else
{
templateType = TemplateEnum.ExcelFileTypeEnum.Type2;
break;
}
}
}
bool skipReadingHeaderRow = rows.MoveNext();
if (templateType == TemplateEnum.ExcelFileTypeEnum.Type1)
{
bool readSupplierRow = rows.MoveNext();
IRow rowSupplier = (XSSFRow)rows.Current;
ICell supplierCell = rowSupplier.GetCell(1);
string supplierName = supplierCell.ToString();
skipReadingHeaderRow = rows.MoveNext();
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
ICell cell1 = row.GetCell(1);
if (cell1 != null)
{
if (String.IsNullOrEmpty(cell1.ToString()))
// all rows was read
break;
else
{
int quantity = 0;
if (row.GetCell(2) != null)
Int32.TryParse(row.GetCell(2).ToString(), out quantity);
decimal price = 0;
if (row.GetCell(3) != null)
{
string priceStr = row.GetCell(3).ToString().Trim().Replace('.', ',');
if(!String.IsNullOrEmpty(priceStr))
price = Convert.ToDecimal(priceStr);
}
decimal discount = 0;
if (row.GetCell(4) != null)
{
string discountStr = row.GetCell(4).ToString().Trim().Replace('.', ',');
if (!String.IsNullOrEmpty(discountStr))
discount = Convert.ToDecimal(discountStr);
}
productLines.Add(
new ProductLine {
ProductArticle = cell1.ToString().Trim(),
Quantity = quantity,
PurchasePrice = price,
Discount = discount,
Comment = (row.GetCell(5) == null ? String.Empty: row.GetCell(5).ToString().Trim()),
SupplierCode = supplierName
}
);
}
}
}
}
else if (templateType == TemplateEnum.ExcelFileTypeEnum.Type2)
{
int emptyRowLimit = 0;
Stack<ProductLine> tempProducts = new Stack<ProductLine>();
string supplierName = String.Empty;
skipReadingHeaderRow = rows.MoveNext();
skipReadingHeaderRow = rows.MoveNext();
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
ICell productArticleCell = row.GetCell(1);
if (productArticleCell != null)
{
if (!String.IsNullOrEmpty(productArticleCell.ToString().Trim()))
{
emptyRowLimit = 0;
// check supplierName
ICell supplierNameCell = row.GetCell(0);
if (supplierNameCell != null)
{
if (!String.IsNullOrEmpty(supplierNameCell.ToString()))
supplierName = supplierNameCell.ToString();
}
int quantity = GetIntCellValue(row.GetCell(2));
// Закупочная цена
decimal price = GetDecimalCellValue(row.GetCell(3));
// Цена
decimal salePrice = GetDecimalCellValue(row.GetCell(4));
decimal discount = GetDecimalCellValue(row.GetCell(7));
tempProducts.Push(
new ProductLine {
ProductArticle = productArticleCell.ToString().Trim(),
Quantity = quantity,
MarginAbs = salePrice -price,
PurchasePrice = price,
Discount = discount,
SalePrice = salePrice
});
}
else
{
// dump products from stack
while(tempProducts.Count > 0)
{
var tempProduct = tempProducts.Pop();
productLines.Add(
new ProductLine {
SupplierCode = supplierName,
ProductArticle = tempProduct.ProductArticle,
Quantity = tempProduct.Quantity,
Discount = tempProduct.Discount,
SalePrice = tempProduct.SalePrice,
PurchasePrice = tempProduct.PurchasePrice,
MarginAbs = tempProduct.MarginAbs
}
);
}
// empty row
if (emptyRowLimit > 3)
break;
else
{
supplierName = String.Empty;
emptyRowLimit++;
}
}
}
}
}
sheet = null;
return productLines;
}
else
return null;
}
private void btnProcess_Click(object sender, EventArgs e)
{
XSSFWorkbook wk;
using (FileStream fs = File.OpenRead(txtPath.Text)) //打开myxls.xls文件
{
wk = new XSSFWorkbook(fs); //把xls文件中的数据写入wk中
}
ISheet sheetLead = wk.GetSheet("Lead");
if (sheetLead == null)
{
MessageBox.Show("Cannot find 'Lead' sheet");
return;
}
ISheet sheetClosedOpp = wk.GetSheet("Closed Opp");
if (sheetClosedOpp == null)
{
MessageBox.Show("Cannot find 'Closed Opp' sheet");
return;
}
ISheet sheetPipeline = wk.GetSheet("Pipeline");
if (sheetPipeline == null)
{
MessageBox.Show("Cannot find 'Pipeline' sheet");
return;
}
var leadData = GetSheetData(sheetLead);
var closedOppData = GetSheetData(sheetClosedOpp);
var pipelineData = GetSheetData(sheetPipeline);
var verticals = new List<string>();
leadData.ForEach(l => {
if (!verticals.Contains(l[0]))
{
verticals.Add(l[0]);
}
});
closedOppData.ForEach(l => {
if (!verticals.Contains(l[0]))
{
verticals.Add(l[0]);
}
});
pipelineData.ForEach(l => {
if (!verticals.Contains(l[0]))
{
verticals.Add(l[0]);
}
});
verticals.Add("Grand Total");
//创建工作薄
XSSFWorkbook wkNew = new XSSFWorkbook();
List<string> leadStatus = new List<string>{ "Leads", "New","Working","Accepted","Converted","Rejected" };
Dictionary<string,string> pipelineStatus = new Dictionary<string, string> { { "ClosedWon", "Closed Won" }, { "ClosedLost", "Closed Lost" }, { "ClosedWonM", "Closed Won" }, { "ClosedLostM", "Closed Lost" }, { "PipelineCreated", "" } };
//创建一个名称为mySheet的表
ISheet tb = wkNew.CreateSheet("ReferenceData");
for (int i = 0; i< verticals.Count;i++)
{
//创建一行，此行为第二行
for (int j = 0; j < leadStatus.Count; j++)
{
IRow row = tb.CreateRow(i* (leadStatus.Count+pipelineStatus.Count)+j);
ICell cell = row.CreateCell(0); //在第二行中创建单元格
cell.SetCellValue(verticals[i]);//循环往第二行的单元格中添加数据
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue(leadStatus[j]);
for (int k = 2; k < 12 + 2; k++)
{
int data = 0;
ICell cellData = row.CreateCell(k);
if (verticals[i] == "Grand Total")
{
if (j == 0)
data = leadData.Where(L => L[1].StartsWith((k - 1).ToString() + "/")).Count();
else
data = leadData.Where(L => L[1].StartsWith((k - 1).ToString() + "/") && L[2] == leadStatus[j]).Count();
cellData.SetCellValue(data);
}
else {
if (j == 0)
data = leadData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/")).Count();
else
data = leadData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/") && L[2] == leadStatus[j]).Count();
cellData.SetCellValue(data);
}
}
}
for (int j = 0; j < pipelineStatus.Count;j++)
{
IRow row = tb.CreateRow(i * (leadStatus.Count + pipelineStatus.Count)+leadStatus.Count + j);
ICell cell = row.CreateCell(0); //在第二行中创建单元格
cell.SetCellValue(verticals[i]);//循环往第二行的单元格中添加数据
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue(pipelineStatus.ElementAt(j).Key);
for (int k = 2; k < 12 + 2; k++)
{
ICell cellData = row.CreateCell(k);
if (j == 0 || j == 1)
{
if(verticals[i]=="Grand Total")
{
int data = closedOppData.Where(L=> L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Count();
cellData.SetCellValue(data);
}
else
{
int data = closedOppData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Count();
cellData.SetCellValue(data);
}
}
else if (j == 2 || j == 3)
{
if (verticals[i] == "Grand Total")
{
double data = closedOppData.Where(L => L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Sum(L =>
{
double ret = 0.0;
double.TryParse(L[4], out ret);
return ret;
});
cellData.SetCellValue(data);
}
else
{
double data = closedOppData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Sum(L =>
{
double ret = 0.0;
double.TryParse(L[4], out ret);
return ret;
});
cellData.SetCellValue(data);
}
}
else
{
if (verticals[i] == "Grand Total")
{
double data = pipelineData.Where(L => L[1].StartsWith((k - 1).ToString() + "/")).Sum(L => { double ret = 0.0; double.TryParse(L[4], out ret); return ret; });
cellData.SetCellValue(data);
}
else
{
double data = pipelineData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/")).Sum(L => { double ret = 0.0; double.TryParse(L[4], out ret); return ret; });
cellData.SetCellValue(data);
}
}
}
}
}
using (FileStream fs1 = File.OpenWrite(Application.StartupPath+@"/myxls.xlsx")) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wkNew.Write(fs1); //向打开的这个xls文件中写入mySheet表并保存。
MessageBox.Show("提示：创建成功！");
}
}
private string ImportStudentPointExcel(HttpPostedFile postedFile, string schoolNo, string banjiName, string examTime, string examType, string examName, List<StudentPointModel> newstudentPoints)
{
try
{
var ext = Path.GetExtension(postedFile.FileName);
ISheet sheet;
if (ext == ".xls")
{
//office 97-2003
HSSFWorkbook wk = new HSSFWorkbook(postedFile.InputStream);
sheet = wk.GetSheet(wk.GetSheetName(0));
}
else if (ext == ".xlsx")
{
//office 2007-now
XSSFWorkbook wk = new XSSFWorkbook(postedFile.InputStream);
sheet = wk.GetSheet(wk.GetSheetName(0));
}
else
{
return "请使用xls或xlsx格式";
}
if (sheet.LastRowNum < 1)
{
return "表内容不能为空";
}
Dictionary<string, int> rowName = new Dictionary<string, int>();
IList<QueryModel> qmList = new List<QueryModel>();
qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo));
if (banjiName != "")
{
qmList.Add(MakeUtil.getQueryModel("D_Name", "'", SqlWhere.WhereOperator.Equal, banjiName));
}
var students = _departStaffEbi.getModelList(qmList, -1);
qmList.Clear();
qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo));
if (banjiName != "")
{
qmList.Add(MakeUtil.getQueryModel("BanjiName", "'", SqlWhere.WhereOperator.Equal, banjiName));
}
qmList.Add(MakeUtil.getQueryModel("ExamType", "'", SqlWhere.WhereOperator.Equal, examType));
qmList.Add(MakeUtil.getQueryModel("ExamName", "'", SqlWhere.WhereOperator.Equal, examName));
var studentpoints = _studentPointEbi.getModelList(qmList, -1);
// if (sheet.LastRowNum > students.Count)
// {
// return "Excel的导入内容比学生人数多";
// }
for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
{
IRow row = sheet.GetRow(j); //读取当前行数据
//先读取表头信息
if (j == 0)
{
if (row != null)
{
for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数
{
ICell cell = row.GetCell(k); //当前表格
if (cell != null && cell.ToString() != "")
{
//获取表头信息
rowName.Add(cell.ToString(), k);
}
}
}
}
else
{
if (row != null)
{
var banji = row.GetCell(rowName["班级名称"]).ToString();
var xinming = row.GetCell(rowName["姓名"]).ToString();
var bianhao = row.GetCell(rowName["编号"]).ToString();
if (banjiName != "" && banji != banjiName)
{
return string.Format("Excel中的班级名称有误,你选择了 {0} 这个班级,Excel中不应该再出现别的班级", banjiName);
}
//根据前四项找出学生的成绩资料
if (!students.Any(p => p.D_Name == banji && p.DS_Name == xinming && p.DS_JID == bianhao))
{
return string.Format("在班级{2}中找不到姓名为{0}编号为{1}的学生", xinming, bianhao, banji);
}
//然后在excel表中如果有相关资料的话便导入
var studentpointList = studentpoints.Where(p => p.BanjiName == banji && p.XinMing == xinming && p.BianHao == bianhao);
if (studentpointList.Any())
{
var studentpoint = studentpointList.FirstOrDefault();
if (studentpoint.TempIsOld)
{
continue;
}
SetCellToStudentPoint(studentpoint, row, rowName);
studentpoint.TempIsOld = true;
newstudentPoints.Add(studentpoint);
}
else
{
var studentpoint = new StudentPointModel();
studentpoint.SchoolNo = schoolNo;
studentpoint.BanjiName = banji;
studentpoint.XinMing = xinming;
studentpoint.BianHao = bianhao;
studentpoint.ExamType = examType;
studentpoint.ExamName = examName;
studentpoint.ExamTime = Convert.ToDateTime(examTime);
SetCellToStudentPoint(studentpoint, row, rowName);
newstudentPoints.Add(studentpoint);
}
}
}
}
if (newstudentPoints.Any())
{
_studentPointEbi.add(newstudentPoints);
}
return "OK";
}
catch (Exception ex)
{
return "Excel格式不正确," + ex.Message;
}
catch
{
return "Excel格式不正确";
}
}
public string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleQuanDTO> hqDTOList, string fileName)
{
if (hqDTOList == null)
{
return string.Empty;
}
string url = "http://blessing.wang/ExcelModel/quanModel.xlsx";
WebRequest request = WebRequest.Create(url);
WebResponse response = request.GetResponse();
Stream stream = response.GetResponseStream();
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("�ij�����");
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(1);
cell.SetCellValue(hqDTOList.Count + "��");
row = sheet.GetRow(2);
cell = row.GetCell(6);
cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
//���õ�Ԫ���ʽ
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int i = 0; i < hqDTOList.Count; i++)
{
row = sheet.CreateRow(5 + i);
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].ContractNo);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Student);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue("");
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].School);
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Online);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Ps);
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue("��");
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Visa);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue("��");
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].ApplicationType);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Author);
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Note);
}
style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = "���ź�";
font.FontHeightInPoints = 9;
style.SetFont(font);
row = sheet.CreateRow(5 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("������ǩ��:");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("�������ǩ����");
cell.CellStyle = style;
cell = row.CreateCell(7);
cell.SetCellValue("�ܾ���/�ֹ��ܲ���ǩ�֣�");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("���֣�������Դ����");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("˵����");
cell.CellStyle = style;
cell = row.CreateCell(1);
cell.SetCellValue("1.�߶��о���");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("5.����");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("9.��ѧ�������������Ϸ���");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("13.�о�ǩʷѧ��ǩ֤��VO��");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("17.ѧ��ǩ֤��VO)");
cell.CellStyle = style;
row = sheet.CreateRow(7 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("2.��ʿ");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("6.����ֻ��������ѧУ");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("10.���ں���");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("14.̽�ס����ѡ���� (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(8 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("3.�Ǹ߶��о���");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("7.���˳���+�Ƽ���");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("11.����Ԥ�ƺ��о���Ԥ��/����");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("15.ѧ������ (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("4.����");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("8.��д���˳������Ƽ��ţ�");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("12.�������");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("16.��顢ѧ�����ʡ����ӡ��žۡ�����ǩ֤ (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(10 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1���İ��������ȣ��������ձ��������ܶ�̬��¼JX-16���İ����������ձ�������˵����������д������ֱ�����ϣ�");
cell.CellStyle = style;
row = sheet.CreateRow(11 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2������������16:00֮ǰ�걨����ȷ�ϡ�ȷ���������������걨");
cell.CellStyle = style;
string path = HttpContext.Current.Server.MapPath("/ExportTemp/");
DirectoryInfo info = new DirectoryInfo(path);
FileInfo[] files = info.GetFiles();
if (files.Length > 0)
{
for (int i = files.Length - 1; i >= 0; i--)
{
files[i].Delete();
}
}
fileName += ".xlsx";
FileStream file = File.Create(path + fileName);
workbook.Write(file);
file.Close();
stream.Close();
return "http://blessing.wang/ExportTemp/" + fileName;
}
