private static bool addRecord(float data)
{
try
{
//close the FileStream before specifying any changes in the HSSFSheet object
FileStream fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("Arkusz1");
fs.Close();
DateTime current = DateTime.Now;
DateTime thisDay = DateTime.Today;
Row row = sheet.CreateRow(sheet.LastRowNum + 1);
row.CreateCell(0).SetCellValue(data);
row.CreateCell(1).SetCellValue(DateTime.Now.ToShortTimeString());
row.CreateCell(2).SetCellValue(thisDay.ToString("d"));
//reinitialize data before having the workbook object write to the Stream
sheet.ForceFormulaRecalculation = true;
fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
templateWorkbook.Write(fs);
fs.Close();
return true;
}
catch
{
return false;
}
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets)
{
if (!filePath.Equals(asset))
continue;
using (FileStream stream = File.Open (filePath, FileMode.Open, FileAccess.Read))
{
var book = new HSSFWorkbook(stream);
foreach (string sheetName in sheetNames)
{
var exportPath = "Assets/Resources/Data/" + sheetName + ".asset";
// check scriptable object
var data = (Entity_daimyo_mst)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_daimyo_mst));
if (data == null)
{
data = ScriptableObject.CreateInstance<Entity_daimyo_mst>();
AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
data.hideFlags = HideFlags.NotEditable;
}
data.param.Clear();
// check sheet
var sheet = book.GetSheet(sheetName);
if (sheet == null)
{
Debug.LogError("[QuestData] sheet not found:" + sheetName);
continue;
}
// add infomation
for (int i=1; i<= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
ICell cell = null;
var p = new Entity_daimyo_mst.Param();
cell = row.GetCell(0); p.daimyoId = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(1); p.daimyoName = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(2); p.colorR = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(3); p.colorG = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(4); p.colorB = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(5); p.busyoId = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(6); p.senryoku = (int)(cell == null ? 0 : cell.NumericCellValue);
data.param.Add(p);
}
// save scriptable object
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
}
}
}
}
static void Main(string[] args)
{
string strFilePath = string.Format("D:\\1.xls");
HSSFWorkbook wk;
using (FileStream fs = new FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite))
{
wk = new HSSFWorkbook(fs);
}
HSSFSheet hst;
hst = (HSSFSheet)wk.GetSheet("執行情形統計表(里辦公處)");
HSSFRow hr;
int dlastrow = 0; dlastrow = hst.LastRowNum;
#region Get Sheet Name
string strSheetname = hst.SheetName;
#endregion Get Sheet Name
hr = (HSSFRow)hst.GetRow(0);
int dLastNum = hr.LastCellNum;
for (int j = 1; j <= hst.LastRowNum; j++)
{
hr = (HSSFRow)hst.GetRow(j);
for (int i = 0; i < dLastNum; i++)//Column
{
string strcell = hr.GetCell(i) == null ? "0" : hr.GetCell(i).ToString();
}
}
}
/// <summary>
/// 读取文件获取合同信息
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static List<Contract> getContractsFromExcel(string fileName)
{
tFileName = fileName;
List<Contract> list = new List<Contract>();
FileStream f = new FileStream(fileName,FileMode.OpenOrCreate);
Workbook workbook = new HSSFWorkbook(f);
Sheet sheet = workbook.GetSheet("合同信息表");
Row headerRow = sheet.GetRow(0);
//判断是否是正确的协议存储文件
if (headerRow.GetCell(0).ToString() != "协议类型")
{
statusLab = "Excel文件：" + fileName + " 不是正确的合同信息数据文件。";
return null;
}
//读取协议内容
for (int i = 1; i <= sheet.LastRowNum; i++)
{
Row dataRow = sheet.GetRow(i);
Contract con = getContractFromRow(dataRow);
if (con != null)
{
list.Add(con);
}
}
f.Close();
f.Dispose();
return list;
}
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
HSSFSheet sheet = workbook.GetSheet(SheetName);
DataTable table = new DataTable();
HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
dataRow[j] = row.GetCell(j).ToString();
}
ExcelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
public override void ExecuteResult(ControllerContext context)
{
var fs = new FileStream(context.HttpContext.Server.MapPath(
@"\Content\UpdatePeople.xls"), FileMode.Open, FileAccess.Read);
var wb = new HSSFWorkbook(fs, true);
var sheet = wb.GetSheet("Sheet1");
var r = 1;
foreach (var p in UpdatePeopleRows())
{
var row = sheet.CreateRow(r++);
var c = 0;
row.CreateCell(c++).SetCellValue(p.PeopleId);
row.CreateCell(c++).SetCellValue(p.Title);
row.CreateCell(c++).SetCellValue(p.First);
row.CreateCell(c++).SetCellValue(p.GoesBy);
row.CreateCell(c++).SetCellValue(p.Last);
row.CreateCell(c++).SetCellValue(p.Suffix);
row.CreateCell(c++).SetCellValue(p.Email1);
row.CreateCell(c++).SetCellValue(p.Email2);
row.CreateCell(c++).SetCellValue(p.Gender);
if (p.BirthDate.HasValue)
row.CreateCell(c++).SetCellValue(p.BirthDate.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
if (p.Anniversary.HasValue)
row.CreateCell(c++).SetCellValue(p.Anniversary.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
if (p.Joined.HasValue)
row.CreateCell(c++).SetCellValue(p.Joined.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
row.CreateCell(c++).SetCellValue(p.Cell);
row.CreateCell(c++).SetCellValue(p.Work);
row.CreateCell(c++).SetCellValue(p.Member);
if (p.Grade.HasValue)
row.CreateCell(c++).SetCellValue(p.Grade.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
row.CreateCell(c++).SetCellValue(p.Marital);
row.CreateCell(c++).SetCellValue(p.FamilyPos);
row.CreateCell(c++).SetCellValue(p.AltName);
row.CreateCell(c++).SetCellValue(p.Campus);
row.CreateCell(c++).SetCellValue(p.School);
row.CreateCell(c++).SetCellValue(p.Occupation);
row.CreateCell(c++).SetCellValue(p.Employer);
if (p.Deceased.HasValue)
row.CreateCell(c++).SetCellValue(p.Deceased.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
}
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=UpdatePeople.xls");
Response.Charset = "";
wb.Write(Response.OutputStream);
}
public static ISheet GetExcelSheetForXLS(string file, string sheetName)
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheet(sheetName);
return(sheet);
}
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets) {
if (!filePath.Equals (asset))
continue;
Entity_Job data = (Entity_Job)AssetDatabase.LoadAssetAtPath (exportPath, typeof(Entity_Job));
if (data == null) {
data = ScriptableObject.CreateInstance<Entity_Job> ();
AssetDatabase.CreateAsset ((ScriptableObject)data, exportPath);
data.hideFlags = HideFlags.NotEditable;
}
data.sheets.Clear ();
using (FileStream stream = File.Open (filePath, FileMode.Open, FileAccess.Read)) {
IWorkbook book = new HSSFWorkbook (stream);
foreach(string sheetName in sheetNames) {
ISheet sheet = book.GetSheet(sheetName);
if( sheet == null ) {
Debug.LogError("[QuestData] sheet not found:" + sheetName);
continue;
}
Entity_Job.Sheet s = new Entity_Job.Sheet ();
s.name = sheetName;
for (int i=1; i<= sheet.LastRowNum; i++) {
IRow row = sheet.GetRow (i);
ICell cell = null;
Entity_Job.Param p = new Entity_Job.Param ();
cell = row.GetCell(0); p.id = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(1); p.name = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(2); p.lv = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(3); p.difficult = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(4); p.attack = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(5); p.defens = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(6); p.sp = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(7); p.cooltime = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(8); p.casttime = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(9); p.effect = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(10); p.point = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(11); p.bonus = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(12); p.type = (int)(cell == null ? 0 : cell.NumericCellValue);
s.list.Add (p);
}
data.sheets.Add(s);
}
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath (exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty (obj);
}
}
public string ReadExcle(string path = @"~/up/b.xls", int from = 1)
{
HSSFWorkbook _book = new HSSFWorkbook();
string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path);
FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
IWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheet("Sheet1");
string guid = Guid.NewGuid().ToString();
string saveFileName = xlsPath.Path(guid);
StringBuilder sb2 = new StringBuilder();
string courty = "";
//获取sheet的首行
var headerRow = sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
//for (int i = headerRow.FirstCellNum; i < cellCount; i++)
//{
//}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row != null)
{
courty = row.GetCell(0).ToString();
if (!string.IsNullOrWhiteSpace(courty))
{
courty = courty.Split(' ')[0];
sb2.Append(string.Format("'{0}',", courty));
}
}
}
var da = sb2.ToString();
hssfworkbook = null;
sheet = null;
//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return string.Format("../../up/{0}.xls", guid);
//记录日志
}
public void AddExcelUserNPOI()
{
FileStream file = new FileStream(@"d:\temp\test.xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfWork = new HSSFWorkbook(file);
ISheet iSheet = hssfWork.GetSheet("new sheet");
iSheet.CreateRow(iSheet.LastRowNum + 1).CreateCell(0).SetCellValue("uuuu");
FileStream fss = new FileStream(@"d:\temp\test.xls", FileMode.Create);
hssfWork.Write(fss);
file.Close();
}
/// <summary>
/// Excel文档流转换成DataTable
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="sheetName">表名称</param>
/// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
{
DataTable table = null;
using (excelFileStream)
{
IWorkbook workbook = new HSSFWorkbook(excelFileStream);
ISheet sheet = workbook.GetSheet(sheetName);
table = RenderFromExcel(sheet, headerRowIndex);
}
return table;
}
public static void Init()
{
try
{
File = new FileStream(dataFolder + @"\DataSpelers.xls", FileMode.Open, FileAccess.Read);
Workbook = new HSSFWorkbook(File);
Sheet = Workbook.GetSheet("Users");
}
catch (IOException)
{
MessageBox.Show("DataSpelers.xls is open, gelieve deze te sluiten en het programma opnieuw op te starten.", "Excel lees fout");
Environment.Exit(0);
}
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets) {
if (!filePath.Equals (asset))
continue;
QuestRarityData data = (QuestRarityData)AssetDatabase.LoadAssetAtPath (exportPath, typeof(QuestRarityData));
if (data == null) {
data = ScriptableObject.CreateInstance<QuestRarityData> ();
AssetDatabase.CreateAsset ((ScriptableObject)data, exportPath);
data.hideFlags = HideFlags.NotEditable;
}
data.sheets.Clear ();
using (FileStream stream = File.Open (filePath, FileMode.Open, FileAccess.Read)) {
IWorkbook book = new HSSFWorkbook (stream);
foreach(string sheetName in sheetNames) {
ISheet sheet = book.GetSheet(sheetName);
if( sheet == null ) {
Debug.LogError("[QuestData] sheet not found:" + sheetName);
continue;
}
QuestRarityData.Sheet s = new QuestRarityData.Sheet ();
s.name = sheetName;
for (int i=1; i< sheet.LastRowNum; i++) {
IRow row = sheet.GetRow (i);
ICell cell = null;
QuestRarityData.Param p = new QuestRarityData.Param ();
cell = row.GetCell(0); p.kind = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(1); p.id = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(2); p.name = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(3); p.minStep = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(4); p.maxStep = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(5); p.rarity = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(6); p.rarityType = (int)(cell == null ? 0 : cell.NumericCellValue);
s.list.Add (p);
}
data.sheets.Add(s);
}
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath (exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty (obj);
}
}
public FileResult aj_excelEquipmentDown(q_Equipment_Chiller q)
{
HSSFWorkbook wbXLS;
FileStream fileStream = null;
MemoryStream outputStream = new MemoryStream();
try
{
db0 = getDB0();
fileStream = new FileStream(Server.MapPath("~/_Code/RPTExcel/Equipment.xls"), FileMode.Open, FileAccess.ReadWrite);
wbXLS = new HSSFWorkbook(fileStream);
ISheet getSheet = wbXLS.GetSheet("設備表");
Apply_User getUserData = null;
if (this.UserId != null)
{
getUserData = db0.Apply_User.Where(x => x.USERID == this.UserId).FirstOrDefault();
}
SetCellValue(getSheet, "C3", getUserData.USERNAME);
SetCellValue(getSheet, "E2", DateTime.Now.ToString("yyyy/MM/dd"));
SetCellValue(getSheet, "E3", getUserData.USERID);
wbXLS.Write(outputStream);
outputStream.Position = 0;
string setFileName = this.UserId + "-01-設備-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
if (Request.Browser.Browser == "IE" && Request.Browser.Version == "8.0")
{
byte[] bytes = outputStream.ToArray();
Response.Clear();
Response.ContentType = "application/xls";
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(setFileName));
Response.BinaryWrite(bytes);
Response.End();
}
return File(outputStream, "application/vnd.ms-excel", setFileName);
}
catch (Exception ex)
{
Log.Write(ex.Message + ex.StackTrace);
return null;
}
finally
{
db0.Dispose();
}
}
public static Sheet GetSheet(HSSFWorkbook workbook, string sheetName)
{
if (workbook != null)
{
var sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
return sheet;
}
return workbook.CreateSheet(sheetName);
}
return null;
}
public override void FillForm(NPOI.HSSF.UserModel.HSSFWorkbook workBook)
{
base.FillForm(workBook);
HSSFSheet sheet = (HSSFSheet)workBook.GetSheet("台账字段");
int rowCount = HeadCount;
IRow row;
foreach (var item in FillData)
{
row = sheet.GetRow(rowCount);
SetDataRowValue(workBook, row, item);
rowCount++;
}
}
public string ReadExcle(string path = @"~/up/a.xls", int from = 1)
{
HSSFWorkbook _book = new HSSFWorkbook();
string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path);
FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
IWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheet("Sheet1");
string guid = Guid.NewGuid().ToString();
string saveFileName = xlsPath.Path(guid);
StringBuilder sb = new StringBuilder();
string temp="";
//获取sheet的首行
var headerRow = sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
sb.Append( row.GetCell(j).ToString());
}
}
hssfworkbook = null;
sheet = null;
//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return string.Format("../../up/{0}.xls", guid);
//记录日志
}
public void TestPLVRecord1()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(XLS_FILENAME);
HSSFWorkbook workbook = new HSSFWorkbook(is1);
CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1);
IDataValidationConstraint constraint = DVConstraint.CreateFormulaListConstraint(DV_DEFINITION);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint);
// This used to throw an error before
try
{
workbook.GetSheet(SHEET_NAME).AddValidationData(dataValidation);
}
catch (InvalidOperationException)
{
Assert.Fail("Identified bug 53972, PLV record breaks addDataValidation()");
}
}
public static void ImportExcel(string path)
{
HSSFWorkbook wb = new HSSFWorkbook(new FileStream(path, FileMode.Open));
HSSFSheet sht = wb.GetSheet("Sheet1");
//取行Excel的最大行数
int rowsCount = sht.PhysicalNumberOfRows;
//为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
//为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
int colsCount = sht.GetRow(0).PhysicalNumberOfCells;
StringBuilder table = new StringBuilder(rowsCount * 32);
for (int rowIndex = 1; rowIndex < rowsCount; rowIndex++)
{
Guid id = new Guid(sht.GetRow(rowIndex).GetCell(0).StringCellValue);
string logistics = sht.GetRow(rowIndex).GetCell(8).ToString();
WebBLL.UpdateLogistics(id, logistics);
}
}
public void TestBug43093()
{
HSSFWorkbook xlw = new HSSFWorkbook();
AddNewSheetWithCellsA1toD4(xlw, 1);
AddNewSheetWithCellsA1toD4(xlw, 2);
AddNewSheetWithCellsA1toD4(xlw, 3);
AddNewSheetWithCellsA1toD4(xlw, 4);
NPOI.SS.UserModel.ISheet s2 = xlw.GetSheet("s2");
IRow s2r3 = s2.GetRow(3);
ICell s2E4 = s2r3.CreateCell(4);
s2E4.CellFormula = ("SUM(s3!B2:C3)");
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(xlw);
double d = eva.Evaluate(s2E4).NumberValue;
// internalEvaluate(...) Area3DEval.: 311+312+321+322 expected
Assert.AreEqual(d, (311 + 312 + 321 + 322), 0.0000001);
// System.out.println("Area3DEval ok.: 311+312+321+322=" + d);
}
public void TestCaseInsensitiveNames()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet originalSheet = b.CreateSheet("Sheet1");
NPOI.SS.UserModel.ISheet fetchedSheet = b.GetSheet("sheet1");
if (fetchedSheet == null)
{
throw new AssertionException("Identified bug 44892");
}
Assert.AreEqual(originalSheet, fetchedSheet);
try
{
b.CreateSheet("sHeeT1");
Assert.Fail("should have thrown exceptiuon due to duplicate sheet name");
}
catch (ArgumentException e)
{
// expected during successful Test
Assert.AreEqual("The workbook already contains a sheet of this name", e.Message);
}
}
private async void btnLoad_Click(object sender, EventArgs e)
{
this.btnLoad.Enabled = false;
Stream fs = new FileStream("G:\\JIRA.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite);
IWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheet("All Case");
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; // 一行最后一个cell的编号 即总的列数
int rowCount = sheet.LastRowNum; // 最后一列的标号
int startRow = 1;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
string casenum = row.GetCell(1).ToString();
List<string> cases = new List<string>();
cases.Add(casenum);
var getCaselist = SalesforceProxy.GetCaseList(cases);
var caseList = await getCaselist;
foreach (var caseInfo in caseList)
{
if(caseInfo != null)
{
System.Console.WriteLine(casenum + "," + caseInfo.Origin);
}
}
}
}
this.btnLoad.Enabled = true;
}
public List<BetType> QueryBetTypes(params int[] betTypeIds)
{
ObjectCache cache = MemoryCache.Default;
var betTypes = (List<BetType>)cache.Get("BetTypes");
if (betTypes == null || !betTypes.Any())
{
betTypes = new List<BetType>();
using (var input = new StreamReader(BetTypeFilePath))
{
IWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream));
ISheet sheet = workbook.GetSheet("BetTypes");
int lastRowNumber = sheet.LastRowNum;
for (int rowNum = 2; rowNum <= lastRowNumber; rowNum++)
{
var betType = CreateBetTypeFromExcelRow(sheet.GetRow(rowNum));
betTypes.Add(betType);
}
}
CacheItemPolicy policy = new CacheItemPolicy();
policy.ChangeMonitors.Add(new HostFileChangeMonitor(new List<string> { BetTypeFilePath }));
cache.Add("BetTypes", betTypes, policy);
}
if (betTypeIds.Length > 0)
{
return betTypes
.Where(betType => betTypeIds.Contains(betType.Id))
.Select(betType => betType)
.ToList();
}
return betTypes;
}
public override void ExecuteResult(ControllerContext context)
{
var fs = new FileStream(context.HttpContext.Server.MapPath(
"/Content/MucketyTemplate.xls"), FileMode.Open, FileAccess.Read);
var wb = new HSSFWorkbook(fs, true);
var sheet = wb.GetSheet("Sheet1");
var r = 1;
foreach (var p in FetchMucketyMapOrgs())
{
ExecuteResultExtracted(sheet, r, p);
r++;
}
foreach (var p in FetchMucketyMapFamily())
{
ExecuteResultExtracted(sheet, r, p);
r++;
}
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=muckety.xls");
Response.Charset = "";
wb.Write(Response.OutputStream);
}
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格式不正确";
}
}
protected void ButtonImportSave_Click(object sender, EventArgs e)
{
try
{
string strFilePath = GetImpFileName();
if (strFilePath == string.Empty)
{
return;
}
this.dataSet = new DataSet();
this.dataSet.Clear();
this.dataSet.Tables.Add("WFM_Import");
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("ErrorMsg", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("orgcode", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("orgname", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("billtypename", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("Overtimetype", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("LeaveDays", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("Shiwei", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("Manger", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("leaveType", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("outtype", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("outtypeDays", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman1", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname1", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename1", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype1", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman2", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname2", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename2", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype2", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman3", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname3", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename3", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype3", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman4", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname4", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename4", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype4", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman5", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname5", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename5", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype5", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman6", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname6", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename6", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype6", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman7", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname7", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename7", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype7", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman8", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname8", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename8", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype8", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman9", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname9", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename9", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype9", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman10", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname10", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename10", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype10", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman11", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname11", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename11", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype11", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman12", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname12", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename12", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype12", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman13", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname13", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename13", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype13", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman14", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname14", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename14", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype14", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman15", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname15", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename15", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype15", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman16", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname16", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename16", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype16", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman17", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname17", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename17", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype17", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman18", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname18", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename18", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype18", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman19", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname19", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename19", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype19", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditman20", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("localname20", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("audittypename20", typeof(string)));
this.dataSet.Tables["WFM_Import"].Columns.Add(new DataColumn("auditmantype20", typeof(string)));
string OrgCode = string.Empty;
string OrgName = string.Empty;
string BillTypeName = string.Empty;
string errorMsg = string.Empty;
string Overtimetype = string.Empty;
string LeaveDays = string.Empty;
string Shiwei = string.Empty;
string Manger = string.Empty;
string leaveType = string.Empty;
string outtype = string.Empty;
string outtypeDays = string.Empty;
string[,] AuditMan = new string[20, 4];
string WorkNo = string.Empty;
string AuditTypeName = string.Empty;
string AuditManType = string.Empty;
string AuditMan1 = string.Empty;
string LocalName1 = string.Empty;
string AuditTypeName1 = string.Empty;
string AuditManType1 = string.Empty;
string AuditMan2 = string.Empty;
string LocalName2 = string.Empty;
string AuditTypeName2 = string.Empty;
string AuditManType2 = string.Empty;
string AuditMan3 = string.Empty;
string LocalName3 = string.Empty;
string AuditTypeName3 = string.Empty;
string AuditManType3 = string.Empty;
string AuditMan4 = string.Empty;
string LocalName4 = string.Empty;
string AuditTypeName4 = string.Empty;
string AuditManType4 = string.Empty;
string AuditMan5 = string.Empty;
string LocalName5 = string.Empty;
string AuditTypeName5 = string.Empty;
string AuditManType5 = string.Empty;
string AuditMan6 = string.Empty;
string LocalName6 = string.Empty;
string AuditTypeName6 = string.Empty;
string AuditManType6 = string.Empty;
string AuditMan7 = string.Empty;
string LocalName7 = string.Empty;
string AuditTypeName7 = string.Empty;
string AuditManType7 = string.Empty;
string AuditMan8 = string.Empty;
string LocalName8 = string.Empty;
string AuditTypeName8 = string.Empty;
string AuditManType8 = string.Empty;
string AuditMan9 = string.Empty;
string LocalName9 = string.Empty;
string AuditTypeName9 = string.Empty;
string AuditManType9 = string.Empty;
string AuditMan10 = string.Empty;
string LocalName10 = string.Empty;
string AuditTypeName10 = string.Empty;
string AuditManType10 = string.Empty;
string AuditMan11 = string.Empty;
string LocalName11 = string.Empty;
string AuditTypeName11 = string.Empty;
string AuditManType11 = string.Empty;
string AuditMan12 = string.Empty;
string LocalName12 = string.Empty;
string AuditTypeName12 = string.Empty;
string AuditManType12 = string.Empty;
string AuditMan13 = string.Empty;
string LocalName13 = string.Empty;
string AuditTypeName13 = string.Empty;
string AuditManType13 = string.Empty;
string AuditMan14 = string.Empty;
string LocalName14 = string.Empty;
string AuditTypeName14 = string.Empty;
string AuditManType14 = string.Empty;
string AuditMan15 = string.Empty;
string LocalName15 = string.Empty;
string AuditTypeName15 = string.Empty;
string AuditManType15 = string.Empty;
string AuditMan16 = string.Empty;
string LocalName16 = string.Empty;
string AuditTypeName16 = string.Empty;
string AuditManType16 = string.Empty;
string AuditMan17 = string.Empty;
string LocalName17 = string.Empty;
string AuditTypeName17 = string.Empty;
string AuditManType17 = string.Empty;
string AuditMan18 = string.Empty;
string LocalName18 = string.Empty;
string AuditTypeName18 = string.Empty;
string AuditManType18 = string.Empty;
string AuditMan19 = string.Empty;
string LocalName19 = string.Empty;
string AuditTypeName19 = string.Empty;
string AuditManType19 = string.Empty;
string AuditMan20 = string.Empty;
string LocalName20 = string.Empty;
string AuditTypeName20 = string.Empty;
string AuditManType20 = string.Empty;
HSSFWorkbook hssfworkbook;
StringBuilder sb = new StringBuilder(20000);
using (Stream fileStream = new FileStream(strFilePath, FileMode.Open))
{
hssfworkbook = new HSSFWorkbook(fileStream);
}
Sheet insertSheet = hssfworkbook.GetSheet("Sheet1");
int nRows = 1;
int index = 0;
int errorCount = 0;
while (insertSheet.GetRow(nRows) != null)
{
try
{
Row dr = insertSheet.GetRow(nRows);
OrgCode = dr.GetCell(0) == null ? string.Empty : dr.GetCell(0).ToString().Trim();
OrgName = dr.GetCell(1) == null ? string.Empty : dr.GetCell(1).ToString().Trim();
BillTypeName = dr.GetCell(2) == null ? string.Empty : dr.GetCell(2).ToString().Trim();
Overtimetype = dr.GetCell(3) == null ? string.Empty : dr.GetCell(3).ToString().Trim();
LeaveDays = dr.GetCell(4) == null ? string.Empty : dr.GetCell(4).ToString().Trim();
if (LeaveDays != string.Empty)
{
LeaveDays = LeaveDays.Substring(0, LeaveDays.IndexOf("["));
}
Shiwei = dr.GetCell(5) == null ? string.Empty : dr.GetCell(5).ToString().Trim();
if (Shiwei != string.Empty)
{
Shiwei = Shiwei.Substring(0, Shiwei.IndexOf("["));
}
Manger = dr.GetCell(6) == null ? string.Empty : dr.GetCell(6).ToString().Trim();
if (Manger != string.Empty)
{
Manger = Manger.Substring(0, Manger.IndexOf("["));
}
leaveType = dr.GetCell(7) == null ? string.Empty : dr.GetCell(7).ToString().Trim();
if (leaveType != string.Empty)
{
leaveType = leaveType.Substring(0, leaveType.IndexOf("["));
}
outtype = dr.GetCell(8) == null ? string.Empty : dr.GetCell(8).ToString().Trim();
if (outtype != string.Empty)
{
outtype = outtype.Substring(0, outtype.IndexOf("["));
}
outtypeDays = dr.GetCell(9) == null ? string.Empty : dr.GetCell(9).ToString().Trim();
if (outtypeDays != string.Empty)
{
outtypeDays = outtypeDays.Substring(0, outtypeDays.IndexOf("["));
}
AuditMan1 = dr.GetCell(10) == null ? string.Empty : dr.GetCell(10).ToString().Trim();
AuditMan1 = dr.GetCell(11) == null ? string.Empty : dr.GetCell(11).ToString().Trim();
LocalName1 = dr.GetCell(12) == null ? string.Empty : dr.GetCell(12).ToString().Trim();
AuditTypeName1 = dr.GetCell(13) == null ? string.Empty : dr.GetCell(13).ToString().Trim();
AuditManType1 = dr.GetCell(14) == null ? string.Empty : dr.GetCell(14).ToString().Trim();
AuditMan2 = dr.GetCell(15) == null ? string.Empty : dr.GetCell(15).ToString().Trim();
LocalName2 = dr.GetCell(16) == null ? string.Empty : dr.GetCell(16).ToString().Trim();
AuditTypeName2 = dr.GetCell(17) == null ? string.Empty : dr.GetCell(17).ToString().Trim();
AuditManType2 = dr.GetCell(18) == null ? string.Empty : dr.GetCell(18).ToString().Trim();
AuditMan3 = dr.GetCell(19) == null ? string.Empty : dr.GetCell(19).ToString().Trim();
LocalName3 = dr.GetCell(20) == null ? string.Empty : dr.GetCell(20).ToString().Trim();
AuditTypeName3 = dr.GetCell(21) == null ? string.Empty : dr.GetCell(21).ToString().Trim();
AuditManType3 = dr.GetCell(22) == null ? string.Empty : dr.GetCell(22).ToString().Trim();
AuditMan4 = dr.GetCell(23) == null ? string.Empty : dr.GetCell(23).ToString().Trim();
LocalName4 = dr.GetCell(24) == null ? string.Empty : dr.GetCell(24).ToString().Trim();
AuditTypeName4 = dr.GetCell(25) == null ? string.Empty : dr.GetCell(25).ToString().Trim();
AuditManType4 = dr.GetCell(26) == null ? string.Empty : dr.GetCell(26).ToString().Trim();
AuditMan5 = dr.GetCell(27) == null ? string.Empty : dr.GetCell(27).ToString().Trim();
LocalName5 = dr.GetCell(28) == null ? string.Empty : dr.GetCell(28).ToString().Trim();
AuditTypeName5 = dr.GetCell(29) == null ? string.Empty : dr.GetCell(29).ToString().Trim();
AuditManType5 = dr.GetCell(30) == null ? string.Empty : dr.GetCell(30).ToString().Trim();
AuditMan6 = dr.GetCell(31) == null ? string.Empty : dr.GetCell(31).ToString().Trim();
LocalName6 = dr.GetCell(32) == null ? string.Empty : dr.GetCell(32).ToString().Trim();
AuditTypeName6 = dr.GetCell(33) == null ? string.Empty : dr.GetCell(33).ToString().Trim();
AuditManType6 = dr.GetCell(34) == null ? string.Empty : dr.GetCell(34).ToString().Trim();
AuditMan7 = dr.GetCell(35) == null ? string.Empty : dr.GetCell(35).ToString().Trim();
LocalName7 = dr.GetCell(36) == null ? string.Empty : dr.GetCell(36).ToString().Trim();
AuditTypeName7 = dr.GetCell(37) == null ? string.Empty : dr.GetCell(37).ToString().Trim();
AuditManType7 = dr.GetCell(38) == null ? string.Empty : dr.GetCell(38).ToString().Trim();
AuditMan8 = dr.GetCell(39) == null ? string.Empty : dr.GetCell(39).ToString().Trim();
LocalName8 = dr.GetCell(40) == null ? string.Empty : dr.GetCell(40).ToString().Trim();
AuditTypeName8 = dr.GetCell(41) == null ? string.Empty : dr.GetCell(41).ToString().Trim();
AuditManType8 = dr.GetCell(42) == null ? string.Empty : dr.GetCell(42).ToString().Trim();
AuditMan9 = dr.GetCell(43) == null ? string.Empty : dr.GetCell(43).ToString().Trim();
LocalName9 = dr.GetCell(44) == null ? string.Empty : dr.GetCell(44).ToString().Trim();
AuditTypeName9 = dr.GetCell(45) == null ? string.Empty : dr.GetCell(45).ToString().Trim();
AuditManType9 = dr.GetCell(46) == null ? string.Empty : dr.GetCell(46).ToString().Trim();
AuditMan10 = dr.GetCell(47) == null ? string.Empty : dr.GetCell(47).ToString().Trim();
LocalName10 = dr.GetCell(48) == null ? string.Empty : dr.GetCell(48).ToString().Trim();
AuditTypeName10 = dr.GetCell(49) == null ? string.Empty : dr.GetCell(49).ToString().Trim();
AuditManType10 = dr.GetCell(50) == null ? string.Empty : dr.GetCell(50).ToString().Trim();
AuditMan11 = dr.GetCell(51) == null ? string.Empty : dr.GetCell(51).ToString().Trim();
LocalName11 = dr.GetCell(52) == null ? string.Empty : dr.GetCell(52).ToString().Trim();
AuditTypeName11 = dr.GetCell(53) == null ? string.Empty : dr.GetCell(53).ToString().Trim();
AuditManType11 = dr.GetCell(54) == null ? string.Empty : dr.GetCell(54).ToString().Trim();
AuditMan12 = dr.GetCell(55) == null ? string.Empty : dr.GetCell(55).ToString().Trim();
LocalName12 = dr.GetCell(56) == null ? string.Empty : dr.GetCell(56).ToString().Trim();
AuditTypeName12 = dr.GetCell(57) == null ? string.Empty : dr.GetCell(57).ToString().Trim();
AuditManType12 = dr.GetCell(58) == null ? string.Empty : dr.GetCell(58).ToString().Trim();
AuditMan13 = dr.GetCell(59) == null ? string.Empty : dr.GetCell(59).ToString().Trim();
LocalName13 = dr.GetCell(60) == null ? string.Empty : dr.GetCell(60).ToString().Trim();
AuditTypeName13 = dr.GetCell(61) == null ? string.Empty : dr.GetCell(61).ToString().Trim();
AuditManType13 = dr.GetCell(62) == null ? string.Empty : dr.GetCell(62).ToString().Trim();
AuditMan14 = dr.GetCell(63) == null ? string.Empty : dr.GetCell(63).ToString().Trim();
LocalName14 = dr.GetCell(64) == null ? string.Empty : dr.GetCell(64).ToString().Trim();
AuditTypeName14 = dr.GetCell(65) == null ? string.Empty : dr.GetCell(65).ToString().Trim();
AuditManType14 = dr.GetCell(66) == null ? string.Empty : dr.GetCell(66).ToString().Trim();
AuditMan15 = dr.GetCell(67) == null ? string.Empty : dr.GetCell(67).ToString().Trim();
LocalName15 = dr.GetCell(68) == null ? string.Empty : dr.GetCell(68).ToString().Trim();
AuditTypeName15 = dr.GetCell(69) == null ? string.Empty : dr.GetCell(69).ToString().Trim();
AuditManType15 = dr.GetCell(70) == null ? string.Empty : dr.GetCell(70).ToString().Trim();
AuditMan16 = dr.GetCell(71) == null ? string.Empty : dr.GetCell(71).ToString().Trim();
LocalName16 = dr.GetCell(72) == null ? string.Empty : dr.GetCell(72).ToString().Trim();
AuditTypeName16 = dr.GetCell(73) == null ? string.Empty : dr.GetCell(73).ToString().Trim();
AuditManType16 = dr.GetCell(74) == null ? string.Empty : dr.GetCell(74).ToString().Trim();
AuditMan17 = dr.GetCell(75) == null ? string.Empty : dr.GetCell(75).ToString().Trim();
LocalName17 = dr.GetCell(76) == null ? string.Empty : dr.GetCell(76).ToString().Trim();
AuditTypeName17 = dr.GetCell(77) == null ? string.Empty : dr.GetCell(77).ToString().Trim();
AuditManType17 = dr.GetCell(78) == null ? string.Empty : dr.GetCell(78).ToString().Trim();
AuditMan18 = dr.GetCell(79) == null ? string.Empty : dr.GetCell(79).ToString().Trim();
LocalName18 = dr.GetCell(80) == null ? string.Empty : dr.GetCell(80).ToString().Trim();
AuditTypeName18 = dr.GetCell(81) == null ? string.Empty : dr.GetCell(81).ToString().Trim();
AuditManType18 = dr.GetCell(82) == null ? string.Empty : dr.GetCell(82).ToString().Trim();
AuditMan19 = dr.GetCell(83) == null ? string.Empty : dr.GetCell(83).ToString().Trim();
LocalName19 = dr.GetCell(84) == null ? string.Empty : dr.GetCell(84).ToString().Trim();
AuditTypeName19 = dr.GetCell(85) == null ? string.Empty : dr.GetCell(85).ToString().Trim();
AuditManType19 = dr.GetCell(86) == null ? string.Empty : dr.GetCell(86).ToString().Trim();
AuditMan20 = dr.GetCell(87) == null ? string.Empty : dr.GetCell(87).ToString().Trim();
LocalName20 = dr.GetCell(88) == null ? string.Empty : dr.GetCell(88).ToString().Trim();
AuditTypeName20 = dr.GetCell(89) == null ? string.Empty : dr.GetCell(89).ToString().Trim();
AuditManType20 = dr.GetCell(90) == null ? string.Empty : dr.GetCell(90).ToString().Trim();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('" + Message.IsNotExcel + "');</script>");
return;
}
int t = 0;
int intCount = 10;
while (t < 20)
{
for (int j = 0; j < 4; j++)
{
if (insertSheet.GetRow(nRows).GetCell(intCount) != null)
{
AuditMan[t, j] = insertSheet.GetRow(nRows).GetCell(intCount).ToString().Trim();
}
else
{
AuditMan[t, j] = string.Empty;
}
intCount++;
}
t++;
}
if (OrgCode.Length > 0)
{
int i = wfset.IsExistsDeptCode(OrgCode);
if (i <= 0)
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.deptcodenotexit;
}
}
else
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.deptcodenotisnull;
}
if (BillTypeName.Length > 0)
{
switch (BillTypeName)
{
case "D001":
case "OTMProjectApply":
if (Overtimetype == string.Empty)
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.overtimenotisnull;
}
break;
case "D002":
if (LeaveDays == string.Empty || Shiwei == string.Empty || Manger == string.Empty || leaveType == string.Empty)
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.leaveinfonotisnull;
}
break;
case "D003":
if (outtype == string.Empty || outtypeDays == string.Empty)
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.outawayisnotnull;
}
break;
default:
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.doctypenotexit;
break;
}
}
else
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.doctypenotidnull;
}
Dictionary<int, List<string>> driy = new Dictionary<int, List<string>>();
List<string> exit = new List<string>();
for (t = 0; t < 20; t++)
{
WorkNo = AuditMan[t, 0].ToString();
AuditTypeName = AuditMan[t, 2].ToString();
AuditManType = AuditMan[t, 3].ToString();
if (WorkNo.Length <= 0)
{
break;
}
List<PersonModel> listperson = person.GetPersonUserId(WorkNo);
if (listperson != null && listperson.Count > 0)
{
List<string> personstr = new List<string>();
personstr.Add(WorkNo);
exit.Add(WorkNo);
personstr.Add(listperson[0].Cname);
personstr.Add(listperson[0].Mail);
string manger= wfset.GetManager(WorkNo);
//這里管理職有點問題
personstr.Add(manger);
personstr.Add(AuditManType);
personstr.Add(AuditTypeName);
driy.Add(t, personstr);
}
else
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
errorMsg = errorMsg + Message.supvisor + (t + 1).ToString() + Message.ErrWorkNoNotEXIST;
}
}
List<string> reasonList1 = new List<string>();
switch (BillTypeName)
{
case "D001":
case "OTMProjectApply":
reasonList1.Add(Overtimetype);
break;
case "D002":
reasonList1.Add(LeaveDays);
reasonList1.Add(Shiwei);
reasonList1.Add(Manger);
reasonList1.Add(leaveType);
break;
case "D003":
reasonList1.Add(outtype);
reasonList1.Add(outtypeDays);
break;
}
WorkFlowLimitBll worklimit = new WorkFlowLimitBll();
DataTable dt = worklimit.GetSignLimitInfo(OrgCode, BillTypeName, reasonList1);
List<string> sup = new List<string>();
if (dt != null && dt.Rows.Count > 0)
{
for (int l = 0; l < dt.Rows.Count; l++)
{
string temp = dt.Rows[l]["flow_empno"].ToString();
if (!exit.Contains(temp))
{
sup.Add(temp);
}
}
}
if (sup.Count > 0)
{
if (errorMsg.Length > 0)
{
errorMsg = errorMsg + ",";
}
string temp1 = string.Empty;
foreach (string item in sup)
{
temp1 += item + ",";
}
if (temp1 != string.Empty)
{
temp1 = temp1.Substring(0, temp1.Length - 1);
}
errorMsg = errorMsg + Message.supvisor + temp1 + Message.inthiswayexit;
}
if (errorMsg.Length == 0)
{
if (driy.Count > 0)
{
List<string> reasonList = new List<string>();
switch (BillTypeName)
{
case "D001":
case "OTMProjectApply":
reasonList.Add(Overtimetype);
break;
case "D002":
reasonList.Add(LeaveDays);
reasonList.Add(Shiwei);
reasonList.Add(Manger);
reasonList.Add(leaveType);
break;
case "D003":
reasonList.Add(outtype);
reasonList.Add(outtypeDays);
break;
}
wfset.SaveData(OrgCode, BillTypeName, reasonList, driy);
}
index++;
}
else if (errorMsg.Length > 0)
{
errorCount++;
this.dataSet.Tables["WFM_Import"].Rows.Add(new string[] {
errorMsg, OrgCode, OrgName, BillTypeName,Overtimetype,LeaveDays,Shiwei,Manger,leaveType,outtype,outtypeDays, AuditMan1, LocalName1, AuditTypeName1, AuditManType1, AuditMan2, LocalName2, AuditTypeName2, AuditManType2, AuditMan3, LocalName3, AuditTypeName3, AuditManType3,
AuditMan4, LocalName4, AuditTypeName4, AuditManType4, AuditMan5, LocalName5, AuditTypeName5, AuditManType5, AuditMan6, LocalName6, AuditTypeName6, AuditManType6, AuditMan7, LocalName7, AuditTypeName7, AuditManType7,
AuditMan8, LocalName8, AuditTypeName8, AuditManType8, AuditMan9, LocalName9, AuditTypeName9, AuditManType9, AuditMan10, LocalName10, AuditTypeName10, AuditManType10,AuditMan11, LocalName11, AuditTypeName11, AuditManType11,AuditMan12, LocalName12, AuditTypeName12, AuditManType12,
AuditMan13, LocalName13, AuditTypeName13, AuditManType13,AuditMan14, LocalName14, AuditTypeName14, AuditManType14
});
}
errorMsg = string.Empty;
if (nRows % 250 == 0 || (insertSheet.GetRow(nRows + 1) == null))
{
break;
}
nRows++;
}
this.labeluploadMsg.Text = string.Concat(Message.NumberOfSuccessed, index, " ;", Message.NumberOfFailed, errorCount, " .");
this.UltraWebGridImport.DataSource = this.dataSet.Tables["WFM_Import"].DefaultView;
this.UltraWebGridImport.DataBind();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('" + ex.Message + "');</script>");
}
}
public void TestModify()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("HyperlinksOnManySheets.xls");
NPOI.SS.UserModel.ISheet sheet;
ICell cell;
IHyperlink link;
sheet = wb.GetSheet("WebLinks");
cell = sheet.GetRow(4).GetCell(0);
link = cell.Hyperlink;
//modify the link
link.Address = ("www.apache.org");
//serialize and read again
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
wb = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
sheet = wb.GetSheet("WebLinks");
cell = sheet.GetRow(4).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("www.apache.org", link.Address);
}
public void TestCreate()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
IHyperlink link = new HSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = link;
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = link;
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = link;
//link to a place in this workbook
//Create a target sheet and cell
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = link;
//serialize and read again
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
wb = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("link1.xls", link.Address);
cell = sheet.GetRow(2).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address);
cell = sheet.GetRow(3).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
}
public void TestCreateDocumentLink()
{
HSSFWorkbook wb = new HSSFWorkbook();
//link to a place in this workbook
IHyperlink link;
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//create a target sheet and cell
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
//cell A1 has a link to 'Target Sheet-1'!A1
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.TextMark=("'Target Sheet'!A1");
cell.Hyperlink=(link);
//cell B1 has a link to cell A1 on the same sheet
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address=("'Hyperlinks'!A1");
cell.Hyperlink=(link);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
Assert.AreEqual("'Target Sheet'!A1", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Hyperlinks'!A1", link.TextMark);
Assert.AreEqual("'Hyperlinks'!A1", link.Address);
}
public string aj_excelEvaporateApplyUploadForTest(HttpPostedFileBase file)
{
ReturnAjaxFiles rAjaxResult = new ReturnAjaxFiles();
#region
string tpl_File = string.Empty;
HSSFWorkbook wbXLS;
try
{
db0 = getDB0();
wbXLS = new HSSFWorkbook(file.InputStream);
ISheet getSheet = wbXLS.GetSheet("年度申報表");
var getUserId = GetCellValueString(getSheet, "G3");
if (getUserId != this.UserId)
{
rAjaxResult.result = false;
rAjaxResult.message = "User Id驗證不符";
return defJSON(rAjaxResult);
}
double getYear = GetCellValueInt(getSheet, "C4");
if (getYear > allowApplyYear)
{
rAjaxResult.message = string.Format("{0}年度不充許申報!", getYear);
rAjaxResult.result = false;
return defJSON(rAjaxResult);
}
var isExist = db0.Apply.Any(x => x.y == getYear && x.userid == this.UserId);
int startRow = 7;
int getLastRow = getSheet.LastRowNum;
int apply_id = 0;
Apply apply = new Apply();
if (!isExist)//如果申報表不存在就新增
{
//Apply apply = new Apply();
apply.apply_id = GetNewId(ProcCore.Business.CodeTable.Apply);
apply.doc_date = DateTime.Now;
apply_id = apply.apply_id;//取得id
var getLast_1 = getSheet.GetRow(getLastRow - 1);
apply.doc_name = getLast_1.Cells[1].StringCellValue;
apply.doc_gender = true;
apply.doc_rank = getLast_1.Cells[3].StringCellValue; ;
apply.doc_tel = getLast_1.Cells[5].StringCellValue; ;
var getLast_2 = getSheet.GetRow(getLastRow);
apply.mng_name = getLast_2.Cells[1].StringCellValue;
apply.mng_gender = true;
apply.mng_rank = getLast_2.Cells[3].StringCellValue;
apply.mng_tel = getLast_2.Cells[5].StringCellValue;
apply.y = Convert.ToInt16(getYear);
apply.start_date = DateTime.Parse(getYear + "/01/01");
apply.end_date = DateTime.Parse(getYear + "/12/31");
apply.i_InsertDateTime = DateTime.Now;
apply.userid = this.UserId;
apply.Apply_Detail_Condenser = new List<Apply_Detail_Condenser>();
apply.Apply_Detail_Evaporate = new List<Apply_Detail_Evaporate>();
}
else
{//存在就修改資料
apply_id = db0.Apply.SingleOrDefault(x => x.y == getYear && x.userid == this.UserId).apply_id;//取得id
//apply = await db0.Apply.FindAsync(apply_id);
var getLast_1 = getSheet.GetRow(getLastRow - 1);
apply.doc_name = getLast_1.Cells[1].StringCellValue;
apply.doc_gender = true;
apply.doc_rank = getLast_1.Cells[3].StringCellValue; ;
apply.doc_tel = getLast_1.Cells[5].StringCellValue; ;
var getLast_2 = getSheet.GetRow(getLastRow);
apply.mng_name = getLast_2.Cells[1].StringCellValue;
apply.mng_gender = true;
apply.mng_rank = getLast_2.Cells[3].StringCellValue;
apply.mng_tel = getLast_2.Cells[5].StringCellValue;
apply.i_UpdateDateTime = DateTime.Now;
}
int equiCount = db0.Equipment_Chiller.Where(x => x.USERID == this.UserId).Count();//計算該廠商有幾筆設備
for (int i = 0; i < equiCount; i++)
{
//int monthstart = 0;//起始一月行數
IRow getRow = getSheet.GetRow(startRow);
ICell getCell = getRow.Cells[0];
//取得設備編號
string getEquipmentSN = getRow.Cells[0].StringCellValue;
var checkEquip = db0.Equipment_Chiller.Where(x => x.equipment_sn == getEquipmentSN && x.USERID == this.UserId).Select(x => new { x.equipment_chiller_id, x.equipment_sn }).FirstOrDefault();
if (checkEquip == null)
{
rAjaxResult.result = false;
rAjaxResult.message = "此設備編號不存在:" + getEquipmentSN;
return defJSON(rAjaxResult);
}
//判斷設備是否已申請過
int getEquid = checkEquip.equipment_chiller_id;//取得設備id
Boolean checkDetail = db0.Apply_Detail_Evaporate.Any(x => x.equipment_id == getEquid && x.y == getYear && x.userid == this.UserId);
if (checkDetail)
{
rAjaxResult.result = false;
rAjaxResult.message = "此設備編號已存在:" + getEquipmentSN + ",請點選刪除後再重新上傳";
return defJSON(rAjaxResult);
}
decimal get設計值 = (decimal)getRow.Cells[1].NumericCellValue;
string[] getAbnormal = new string[12];//異常狀況陣列
decimal[] getTd_value = new decimal[12];
//每月資料填入
for (int j = 0; j < 12; j++)
{
getRow = getSheet.GetRow(startRow);
getTd_value[j] = (decimal)getRow.Cells[3].NumericCellValue;
getAbnormal[j] = getRow.Cells[4].StringCellValue;
startRow += 1;
}
Apply_Detail_Evaporate makeEvaporateDetail = new Apply_Detail_Evaporate()
{
apply_detail_evaporate_id = GetNewId(ProcCore.Business.CodeTable.Apply_Detail_Evaporate),
apply_id = apply_id,
equipment_id = checkEquip.equipment_chiller_id,
equipment_sn = checkEquip.equipment_sn,
td_set = get設計值,
abnormal_01 = getAbnormal[0],
abnormal_02 = getAbnormal[1],
abnormal_03 = getAbnormal[2],
abnormal_04 = getAbnormal[3],
abnormal_05 = getAbnormal[4],
abnormal_06 = getAbnormal[5],
abnormal_07 = getAbnormal[6],
abnormal_08 = getAbnormal[7],
abnormal_09 = getAbnormal[8],
abnormal_10 = getAbnormal[9],
abnormal_11 = getAbnormal[10],
abnormal_12 = getAbnormal[11],
td_value_01 = getTd_value[0],
td_value_02 = getTd_value[1],
td_value_03 = getTd_value[2],
td_value_04 = getTd_value[3],
td_value_05 = getTd_value[4],
td_value_06 = getTd_value[5],
td_value_07 = getTd_value[6],
td_value_08 = getTd_value[7],
td_value_09 = getTd_value[8],
td_value_10 = getTd_value[9],
td_value_11 = getTd_value[10],
td_value_12 = getTd_value[11],
i_InsertDateTime = DateTime.Now,
y = Convert.ToInt16(getYear),
userid = this.UserId
};
apply.Apply_Detail_Evaporate.Add(makeEvaporateDetail);
}
if (!isExist)
{
db0.Apply.Add(apply);
}
// await db0.SaveChangesAsync();
rAjaxResult.result = true;
rAjaxResult.success = true;
rAjaxResult.FileName = file.FileName;
}
catch (LogicError ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = getRecMessage(ex.Message);
}
catch (Exception ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = ex.Message + ex.StackTrace;
}
finally
{
db0.Dispose();
}
#endregion
return defJSON(rAjaxResult);
}
public string aj_excelEquipmentUploadForTest(HttpPostedFileBase file)
{
ReturnAjaxFiles rAjaxResult = new ReturnAjaxFiles();
dicEquipmentIsNew = new Dictionary<string, bool>();
dicEquipmentIsNew.Add("即設", false);
dicEquipmentIsNew.Add("新設", true);
#region
string tpl_File = string.Empty;
HSSFWorkbook wbXLS;
try
{
db0 = getDB0();
wbXLS = new HSSFWorkbook(file.InputStream);
ISheet getSheet = wbXLS.GetSheet("設備表");
var getUserId = GetCellValueString(getSheet, "E3");
if (getUserId != this.UserId)
{
rAjaxResult.result = false;
rAjaxResult.message = "User Id驗證不符";
return defJSON(rAjaxResult);
}
int startRow = 5;
int getLastRow = getSheet.LastRowNum;
var getEquipments = db0.Equipment_Chiller.Where(x => x.USERID == this.UserId).ToList();
//匯入之前要先移除設備資料 已在申報中的不移除
foreach (var equip in getEquipments)
{
var checkCIsExist = db0.Apply_Detail_Condenser.Any(x => x.equipment_id == equip.equipment_chiller_id);
var checkEIsExist = db0.Apply_Detail_Evaporate.Any(x => x.equipment_id == equip.equipment_chiller_id);
if (!checkCIsExist && !checkEIsExist)
{
db0.Equipment_Chiller.Remove(equip);
}
}
//await db0.SaveChangesAsync();
for (var i = startRow; i <= getLastRow; i++)
{
IRow getRow = getSheet.GetRow(i);
Equipment_Chiller getImportData = SetEquipmentImportData(getRow);
if (getImportData != null)
{
var isExist = db0.Equipment_Chiller.Any(x => x.equipment_sn == getImportData.equipment_sn);
if (!isExist)
{
db0.Equipment_Chiller.Add(getImportData);
}
}
}
//await db0.SaveChangesAsync();
rAjaxResult.result = true;
rAjaxResult.success = true;
rAjaxResult.FileName = file.FileName;
}
catch (LogicError ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = getRecMessage(ex.Message);
}
catch (Exception ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = ex.Message + ex.StackTrace;
}
finally
{
db0.Dispose();
}
#endregion
return defJSON(rAjaxResult);
}
public async Task<string> aj_excelEquipmentUpload(HttpPostedFileBase file)
{
ReturnAjaxFiles rAjaxResult = new ReturnAjaxFiles();
dicEquipmentIsNew = new Dictionary<string, bool>();
dicEquipmentIsNew.Add("即設", false);
dicEquipmentIsNew.Add("新設", true);
var fileName = "Manager-" + this.UserId + "-設備-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
var path = Path.Combine(Server.MapPath("~/_Code/SysUpFiles/ExcelUpLoadData/Equi"), fileName);
file.SaveAs(path);
string successMessage = string.Empty;
int countSuccessImport = 0;
List<Equipment_Chiller> excelEquipment = new List<Equipment_Chiller>();
#region
string tpl_File = string.Empty;
HSSFWorkbook wbXLS;
try
{
db0 = getDB0();
wbXLS = new HSSFWorkbook(file.InputStream);
ISheet getSheet = wbXLS.GetSheet("設備表");
var getUserId = GetCellValueString(getSheet, "E3");
if (getUserId != this.UserId)
{
rAjaxResult.result = false;
rAjaxResult.message = "User Id驗證不符";
return defJSON(rAjaxResult);
}
int startRow = 5;
int getLastRow = getSheet.LastRowNum;
var getEquipments = db0.Equipment_Chiller.Where(x => x.USERID == this.UserId).ToList();
#region 刪除設備功能(目前不使用)
//匯入之前要先移除設備資料 已在申報中的不移除
//foreach (var equip in getEquipments)
//{
// var checkCIsExist = db0.Apply_Detail_Condenser.Any(x => x.equipment_id == equip.equipment_chiller_id);
// var checkEIsExist = db0.Apply_Detail_Evaporate.Any(x => x.equipment_id == equip.equipment_chiller_id);
// if (!checkCIsExist && !checkEIsExist)
// {
// db0.Equipment_Chiller.Remove(equip);
// }
//}
//await db0.SaveChangesAsync();
#endregion
for (var i = startRow; i <= getLastRow; i++)
{
IRow getRow = getSheet.GetRow(i);
Equipment_Chiller getImportData = null;
try
{
if (getCellValue(getRow, 1) == "" &&
getCellValue(getRow, 2) == "" &&
getCellValue(getRow, 3) == "" &&
getCellValue(getRow, 4) == "")
{//欄位都空白就略過
}
else if (getCellValue(getRow, 1) == "" ||
getCellValue(getRow, 2) == "" ||
getCellValue(getRow, 3) == "" ||
getCellValue(getRow, 4) == "")
{//其中一個直空白不匯入，彈出錯誤訊息告知他資料填寫不完整
rAjaxResult.result = false;
rAjaxResult.message = "Excel檔案第" + (i + 1) + "列設備資料填寫不完整!";
return defJSON(rAjaxResult);
}
getImportData = SetEquipmentImportData(getRow);
}
catch
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = "Excel檔案第" + (i + 1) + "列資料型態錯誤!";
return defJSON(rAjaxResult);
}
if (getImportData != null)
{
var isExist = db0.Equipment_Chiller.Any(x => x.equipment_sn == getImportData.equipment_sn && x.USERID == getImportData.USERID);
var isExcelExist = excelEquipment.Any(x => x.equipment_sn == getImportData.equipment_sn && x.USERID == getImportData.USERID);
if (!isExist && !isExcelExist)
{
if (getImportData.equipment_sn.Length > 20)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = "Excel檔案第" + (i + 1) + "列設備編號長度超過20字!";
return defJSON(rAjaxResult);
}
db0.Equipment_Chiller.Add(getImportData);
excelEquipment.Add(getImportData);
countSuccessImport += 1;
}
else
{
if (isExist)
{
rAjaxResult.message = "Excel檔案第" + (i + 1) + "列設備編號已存在，請勿重複匯入!";
}
else
{
rAjaxResult.message = "Excel檔案第" + (i + 1) + "列，在Excel檔內與其他設備編號重複!";
}
rAjaxResult.result = false;
return defJSON(rAjaxResult);
}
}
}
await db0.SaveChangesAsync();
successMessage = successMessage + "共" + countSuccessImport + "筆資料匯入成功!";
rAjaxResult.result = true;
rAjaxResult.success = true;
rAjaxResult.FileName = file.FileName;
rAjaxResult.message = successMessage;
}
catch (LogicError ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
//rAjaxResult.message = getRecMessage(ex.Message);
rAjaxResult.message = getRecMessage("excel檔案格式錯誤");
}
catch (Exception ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
//rAjaxResult.message = ex.Message + ex.StackTrace;
rAjaxResult.message = getRecMessage("excel檔案格式錯誤");
}
finally
{
db0.Dispose();
}
#endregion
return defJSON(rAjaxResult);
}
