public static void SavePoExcel(List<po> poList, List<List<PoItemContentAndState>> poitemsListList)
{
if (poList.Count() != poitemsListList.Count())
{
MessageBox.Show("Internal Error. Please send the log file to the Author");
Logger.Error(poList.Count() + "," + poitemsListList.Count());
return;
}
FileStream file;
try
{
file = new FileStream(@"PoTemplate.dll", FileMode.Open, FileAccess.Read);
}
catch (Exception)
{
MessageBox.Show("Please check the PoTemplate.dll.");
return;
}
hssfworkbook = new HSSFWorkbook(file);
WriteDsiInfo();
for (int i = 0; i < poList.Count(); i++)
{
ISheet sheet = hssfworkbook.CloneSheet(0);
FillThePoSheet(sheet, poList[i], poitemsListList[i]);
hssfworkbook.SetSheetName(3 + i, "PO" + i.ToString());
}
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
WriteToFile();
}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheetCount"></param>
//protected override void FillData(Microsoft.Office.Interop.Excel.Workbook workBook, int sheetCount)
//{
// int rowCount = Dt.Rows.Count;
// int colCount = Dt.Columns.Count;
// for (int i = 1; i <= sheetCount; i++)
// {
// int startRow = (i - 1) * Rows;
// int endRow = i * Rows;
// if (i == sheetCount)
// {
// endRow = rowCount;
// }
// Worksheet sheet = (Worksheet)workBook.Worksheets.get_Item(i);
// sheet.Name = this.SheetPrefixName + "-" + i.ToString();
// for (int j = 0; j < endRow - startRow; j++)
// {
// for (int k = 0; k < colCount; k++)
// {
// if (k < 2)
// {
// sheet.Cells[Top + j, Left + k] = Dt.Rows[startRow + j][k].ToString();
// }
// else
// {
// sheet.Cells[Top + j, Left + k + 2] = Dt.Rows[startRow + j][k].ToString();
// }
// }
// }
// SetCellParameters(sheet);
// }
//}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheetCount"></param>
protected override void FillData(NPOI.HSSF.UserModel.HSSFWorkbook workBook, int sheetCount)
{
int rowCount = Dt.Rows.Count;
int colCount = Dt.Columns.Count;
for (int i = 0; i < sheetCount; i++)
{
int startRow = i * Rows;
int endRow = (i + 1) * Rows;
if ((i + 1) == sheetCount)
{
endRow = rowCount;
}
HSSFSheet sheet = (HSSFSheet)workBook.GetSheetAt(i);
workBook.SetSheetName(i, this.SheetPrefixName + "-" + i.ToString());
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
if (k < 2)
{
sheet.GetRow(Top + j).GetCell(Left + k).SetCellValue(Dt.Rows[startRow + j][k].ToString());
}
else
{
sheet.GetRow(Top + j).GetCell(Left + k + 2).SetCellValue(Dt.Rows[startRow + j][k].ToString());
}
}
}
SetCellParameters(sheet);
}
}
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
// put some values in the cells to make the evaluation more interesting
sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);
//HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
NPOI.SS.UserModel.CellValue result;
try
{
result = fe.Evaluate(cell);
}
catch (Exception e)
{
if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
{
Assert.Fail("Identifed bug 44539");
}
throw;
}
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, result.CellType);
Assert.AreEqual(42.0, result.NumberValue, 0.0);
}
public void Test3DArea()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
wb.CreateSheet();
wb.SetSheetName(1, "Sheet2");
IRow row = sheet1.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula=("isblank(Sheet2!A1:A1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
cell.CellFormula=("isblank(D7:D7)");
result = fe.Evaluate(cell);
Assert.AreEqual(CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
}
public void TestRangeOperator()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(0);
wb.SetSheetName(0, "Sheet1");
cell.CellFormula = ("Sheet1!B$4:Sheet1!$C1"); // explicit range ':' operator
Assert.AreEqual("Sheet1!B$4:Sheet1!$C1", cell.CellFormula);
cell.CellFormula = ("Sheet1!B$4:$C1"); // plain area ref
Assert.AreEqual("Sheet1!B1:$C$4", cell.CellFormula); // note - area ref is normalised
cell.CellFormula = ("Sheet1!$C1...B$4"); // different syntax for plain area ref
Assert.AreEqual("Sheet1!B1:$C$4", cell.CellFormula);
// with funny sheet name
wb.SetSheetName(0, "A1...A2");
cell.CellFormula = ("A1...A2!B1");
Assert.AreEqual("A1...A2!B1", cell.CellFormula);
wb.Close();
}
[Ignore]// not found in poi
public void Test28031()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
String formulaText =
"IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))";
cell.CellFormula = (formulaText);
Assert.AreEqual(formulaText, cell.CellFormula);
WriteTestOutputFileForViewing(wb, "output28031.xls");
}
[Ignore] ///not found in poi 3.8beat4
public void Test18800()
{
HSSFWorkbook book = new HSSFWorkbook();
book.CreateSheet("TEST");
ISheet sheet = book.CloneSheet(0);
book.SetSheetName(1, "CLONE");
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString("Test"));
book = WriteOutAndReadBack(book);
sheet = book.GetSheet("CLONE");
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell(0);
Assert.AreEqual("Test", cell.RichStringCellValue.String);
}
public ActionResult DownloadResult(string ontologyCode, string fileName)
{
OntologyDescriptor ontology;
if (!AcDomain.NodeHost.Ontologies.TryGetOntology(ontologyCode, out ontology))
{
throw new ValidationException("非法的本体码" + ontologyCode);
}
string dirPath = Server.MapPath("~/Content/Import/Excel/" + ontology.Ontology.Code + "/" + AcSession.Account.Id);
string fullName = Path.Combine(dirPath, fileName);
if (!System.IO.File.Exists(fullName))
{
throw new ValidationException("下载的文件不存在" + fullName);
}
// 操作Excel
FileStream fs = System.IO.File.OpenRead(fullName);
IWorkbook workbook = new HSSFWorkbook(fs);//从流内容创建Workbook对象
fs.Close();
ISheet sheet = workbook.GetSheet(ResultSheetName);
var sheetIndex = workbook.GetSheetIndex(sheet);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
if (i != sheetIndex)
{
workbook.RemoveSheetAt(i);
}
}
sheetIndex = workbook.GetSheetIndex("Failed");
if (sheetIndex >= 0)
{
workbook.RemoveSheetAt(sheetIndex);
}
ISheet failedSheet = workbook.CreateSheet("Failed");
if (sheet.LastRowNum == 2)
{
throw new ValidationException("没有待导入数据");
}
int rowIndex = 0;
IRow headRow0 = sheet.GetRow(rowIndex);
var columnWidthDic = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var cell = headRow0.Cells[i];
columnWidthDic.Add(cell.SafeToStringTrim(), sheet.GetColumnWidth(i));
}
IRow failedRow0 = failedSheet.CreateRow(rowIndex);
var cells = headRow0.Cells;
for (int i = 0; i < cells.Count; i++)
{
var cell = failedRow0.CreateCell(i);
cell.CellStyle = cells[i].CellStyle;
cell.SetCellValue(cells[i].SafeToStringTrim());
}
rowIndex++;
IRow headRow1 = sheet.GetRow(rowIndex);
IRow failedRow1 = failedSheet.CreateRow(rowIndex);
cells = headRow1.Cells;
for (int i = 0; i < cells.Count; i++)
{
var cell = failedRow1.CreateCell(i);
cell.CellStyle = cells[i].CellStyle;
cell.SetCellValue(cells[i].SafeToStringTrim());
}
rowIndex++;
IRow headRow2 = sheet.GetRow(rowIndex);
IRow failedRow2 = failedSheet.CreateRow(rowIndex);
cells = headRow2.Cells;
for (int i = 0; i < cells.Count; i++)
{
var cell = failedRow2.CreateCell(i);
cell.CellStyle = cells[i].CellStyle;
cell.SetCellValue(cells[i].SafeToStringTrim());
}
failedSheet.CreateFreezePane(0, 3, 0, 3);
rowIndex++;
int resultFailedRowIndex = rowIndex;
int stateCodeIndex = -1;
int localEntityIdIndex = -1;
int infoValueKeysIndex = -1;
int infoIdKeysIndex = -1;
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.StateCode.Equals(value, StringComparison.OrdinalIgnoreCase))
{
stateCodeIndex = i;
break;
}
}
if (stateCodeIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$StateCode的列");
}
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.LocalEntityId.Equals(value, StringComparison.OrdinalIgnoreCase))
{
localEntityIdIndex = i;
break;
}
}
if (localEntityIdIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$LocalEntityID的列");
}
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.InfoValueKeys.Equals(value, StringComparison.OrdinalIgnoreCase))
{
infoValueKeysIndex = i;
break;
}
}
if (infoValueKeysIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$InfoValueKeys的列");
}
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.InfoIdKeys.Equals(value, StringComparison.OrdinalIgnoreCase))
{
infoIdKeysIndex = i;
break;
}
}
if (infoIdKeysIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$InfoIDKeys的列");
}
string infoValueKeys = headRow1.GetCell(infoValueKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoValueKeys))
{
throw new ValidationException("$InfoValueKeys单元格无值");
}
string infoIdKeys = headRow1.GetCell(infoIdKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoIdKeys))
{
throw new ValidationException("$InfoIDKeys单元格无值");
}
var selectKeys = new List<string>();
string[] keys = infoIdKeys.Split(',');
if (keys == null || keys.Length == 0)
{
throw new ValidationException("$InfoIDKeys单元格内的值格式错误");
}
selectKeys.AddRange(keys);
keys = infoValueKeys.Split(',');
if (keys == null || keys.Length == 0)
{
throw new ValidationException("$InfoValueKeys单元格内的值格式错误");
}
selectKeys.AddRange(keys);
var entityIDs = new List<string>();
var selectElements = new OrderedElementSet();
foreach (var key in selectKeys)
{
if (!ontology.Elements.ContainsKey(key))
{
throw new ValidationException("Excel文件的$InfoValueKeys单元格内有非法的本体元素码" + key);
}
if (ontology.Elements[key].Element.IsEnabled != 1)
{
continue;
}
selectElements.Add(ontology.Elements[key]);
}
if (ontology.Elements.ContainsKey("LoginName"))
{
if (!selectElements.Contains(ontology.Elements["LoginName"]))
{
selectElements.Add(ontology.Elements["LoginName"]);
}
}
for (int i = rowIndex; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row != null)
{
var stateCodeStr = row.GetCell(stateCodeIndex).SafeToStringTrim();
if (!string.IsNullOrEmpty(stateCodeStr))
{
int stateCode;
if (!int.TryParse(stateCodeStr, out stateCode))
{
throw new AnycmdException("文件" + fullName + "行中有意外的状态码");
}
if (stateCode >= 200 && stateCode < 300)
{
var cell = row.GetCell(localEntityIdIndex);
entityIDs.Add(cell.SafeToStringTrim());
}
if (stateCode < 200 || stateCode >= 300)
{
IRow resultRow = failedSheet.CreateRow(resultFailedRowIndex);
for (int j = 0; j < headRow0.Cells.Count; j++)
{
var cell = resultRow.CreateCell(j);
var oldCell = row.GetCell(j);
if (oldCell != null)
{
cell.CellStyle = oldCell.CellStyle;
cell.SetCellValue(oldCell.SafeToStringTrim());
}
}
resultFailedRowIndex++;
}
}
sheet.RemoveRow(row);
}
rowIndex++;
}
sheet.RemoveRow(headRow0);
sheet.RemoveRow(headRow1);
sheet.RemoveRow(headRow2);
workbook.SetSheetName(workbook.GetSheetIndex(sheet), "Success");
#region Success 数据
rowIndex = 0;
var headRow = sheet.CreateRow(rowIndex);
sheet.CreateFreezePane(0, 1, 0, 1);
rowIndex++;
ICellStyle helderStyle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 14;
helderStyle.SetFont(font);
helderStyle.BorderBottom = BorderStyle.Thin;
helderStyle.BorderLeft = BorderStyle.Thin;
helderStyle.BorderRight = BorderStyle.Thin;
helderStyle.BorderTop = BorderStyle.Thin;
helderStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
helderStyle.FillPattern = FillPattern.SolidForeground;
int cellIndex = 0;
foreach (var element in selectElements)
{
ICell cell = headRow.CreateCell(cellIndex, CellType.String);
sheet.SetColumnHidden(cellIndex, hidden: false);
if (element.IsCodeValue)
{
cell.SetCellValue(element.Element.Name + "码");
}
else
{
cell.SetCellValue(element.Element.Name);
}
if (!string.IsNullOrEmpty(element.Element.Description))
{
//添加批注
IDrawing draw = sheet.CreateDrawingPatriarch();
IComment comment = draw.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 8));//里面参数应该是指示批注的位置大小吧
comment.String = new HSSFRichTextString(element.Element.Description);//添加批注内容
comment.Author = AcDomain.NodeHost.Nodes.ThisNode.Name;//添加批注作者
cell.CellComment = comment;//将之前设置的批注给定某个单元格
}
cell.CellStyle = helderStyle;
if (columnWidthDic.ContainsKey(element.Element.Code) && columnWidthDic[element.Element.Code] > 0)
{
sheet.SetColumnWidth(cellIndex, columnWidthDic[element.Element.Code]);
}
else if (element.Element.Width > 0)
{
sheet.SetColumnWidth(cellIndex, element.Element.Width * 256 / 5);
}
if (element.IsCodeValue)
{
cellIndex++;
ICell nameCell = headRow.CreateCell(cellIndex, CellType.String);
sheet.SetColumnHidden(cellIndex, hidden: false);
nameCell.SetCellValue(element.Element.Name + "名");
nameCell.CellStyle = helderStyle;
if (columnWidthDic.ContainsKey(element.Element.Code) && columnWidthDic[element.Element.Code] > 0)
{
sheet.SetColumnWidth(cellIndex, columnWidthDic[element.Element.Code]);
}
else if (element.Element.Width > 0)
{
sheet.SetColumnWidth(cellIndex, element.Element.Width * 256 / 5);
}
}
cellIndex++;
}
if (entityIDs.Count > 0)
{
DataTuple infoValues = ontology.EntityProvider.GetList(ontology, selectElements, entityIDs);
foreach (var record in infoValues.Tuples)
{
var row = sheet.CreateRow(rowIndex);
int j = 0;
for (int col = 0; col < infoValues.Columns.Count; col++)
{
var element = infoValues.Columns[col];
var item = record[col];
ICell cell = row.CreateCell(j, CellType.String);
cell.SetCellValue(item.ToString());
if (element.IsCodeValue)
{
j++;
ICell nameCell = row.CreateCell(j, CellType.String);
nameCell.SetCellValue(element.TranslateValue(item.ToString()));
}
j++;
}
rowIndex++;
}
}
#endregion
var filestream = new MemoryStream(); //内存文件流(应该可以写成普通的文件流)
workbook.Write(filestream); //把文件读到内存流里面
const string contentType = "application/vnd.ms-excel";
fileName = fileName.Substring(0, fileName.Length - 36 - ".xls".Length);
fileName += "_result";
if (Request.Browser.Type.IndexOf("IE", StringComparison.OrdinalIgnoreCase) > -1)
{
fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
}
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
Response.Clear();
return new FileContentResult(filestream.GetBuffer(), contentType);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="ds">要导出的数据源</param>
/// <param name="excelFilePath">文件的物理路径</param>
/// <param name="errorFun"></param>
/// <returns></returns>
public static bool Output(DataSet ds, string excelFilePath, Action<Exception> errorFun = null)
{
if (!File.Exists(excelFilePath))
{
throw new Exception("Excel 文件不存在");
}
if (null == ds || ds.Tables.Count == 0)
{
return false;
}
try
{
//1.0 创建工作薄 和 工作表对象
NPOI.HSSF.UserModel.HSSFWorkbook book;
using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite))
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile);
}
int sheetIndex = 0;
foreach (DataTable dt in ds.Tables)
{
string sheetName = dt.TableName ?? "sheet" + sheetIndex;
NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表
if (null == sheet1)
{
sheet1 = book.CreateSheet(sheetName);
}
else if (sheet1.SheetName != sheetName)
{
book.SetSheetName(sheetIndex, sheetName);
}
//2.0给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行
for (int i = 0; i < dt.Columns.Count; i++)
{
rowHead.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//3.0 填充表格数据
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);//创建数据行
for (int j = 0; j < dt.Columns.Count; j++)//填充行数据
{
rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
sheetIndex++;
}
//4.0 写入文件
using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create))
{
book.Write(wfile);
}
return true;
}
catch (Exception ex)
{
if (errorFun != null)
{
errorFun(ex);
}
return false;
}
}
public void TestDuplicateNames()
{
HSSFWorkbook b = new HSSFWorkbook();
b.CreateSheet("Sheet1");
b.CreateSheet();
b.CreateSheet("name1");
try
{
b.CreateSheet("name1");
Assert.Fail();
}
catch (ArgumentException)// pass
{
}
b.CreateSheet();
try
{
b.SetSheetName(3, "name1");
Assert.Fail();
}
catch (ArgumentException)// pass
{
}
try
{
b.SetSheetName(3, "name1");
Assert.Fail();
}
catch (ArgumentException)// pass
{
}
b.SetSheetName(3, "name2");
b.SetSheetName(3, "name2");
b.SetSheetName(3, "name2");
HSSFWorkbook c = new HSSFWorkbook();
c.CreateSheet("Sheet1");
c.CreateSheet("Sheet2");
c.CreateSheet("Sheet3");
c.CreateSheet("Sheet4");
}
public void CreateReportAll()
{
LocalReport report = new LocalReport();
string mimeType, encoding, fileExtension;
string[] streams;
Warning[] warnings;
int year = Int32.Parse(Session["year"].ToString());
ReportDataSource rds = new ReportDataSource();
rds.Name = "SampleAnalize";
string username = User.Identity.Name;
UserInfos userinfos = UserInfos.getUserInfos(username);
int fermaid = GetFermaId(userinfos.UserCod);
if (fermaid != 0)
{
List<SampleAnalize> samples = SampleAnalize.GetSampleAnalize(fermaid,"",year);
if (samples.Count > 0)
{
string[] parIds = { "g", "p", "c", "l", "s", "h", "u", "n", "z" };
string[] chartLabelsName = { "Grasime (g/100g)", "Proteine (g/100g)", "Cazeina (g/100g)", "Lactoza (g/100g)", "SUN (g/100g)", "pH", "Uree (mg/dl)", "NCSx1000", "Cantitate lapte (l)" };
string[] chartTitles = { "Grasime", "Proteine", "Cazeina",
"Lactoza", "SUN", "Ph",
"Uree", "NCS","Cantitate lapte"};
string[] um = { "g/100g", "g/100g", "g/100g", "g/100g", "g/100g", "pH", "mg/dl", "NCSx1000", "l" };
ContentPlaceHolder holder = (ContentPlaceHolder)Master.FindControl("MainContent");
for (int i = 0; i < 1 ; i++)
{//parIds.Length
string endChar = parIds[i].ToUpper();
CheckBox chk = (CheckBox)holder.FindControl("chk" + endChar);
if (chk.Checked)
{
//set current param
foreach (SampleAnalize sample in samples)
{
switch (i)
{
case 0:
sample.Val = sample.Grasime; break;
case 1:
sample.Val = sample.Proteine; break;
case 2:
sample.Val = sample.Caseina; break;
case 3:
sample.Val = sample.Lactoza; break;
case 4:
sample.Val = sample.Solide; break;
case 5:
sample.Val = sample.Ph; break;
case 6:
sample.Val = sample.Urea; break;
case 7:
sample.Val = sample.Ncs; break;
case 8:
sample.Val = sample.Cantitate; break;
default: break;
}
sample.Val = Math.Round(sample.Val, 2);
}
// create samplescontrol from samples
// set reports datasource
rds.Value = samples;
report.DataSources.Clear();
report.DataSources.Add(rds);
//
ReportDataSource rdsc = new ReportDataSource();
rdsc.Name = "SampleAnalizeControl";
List<SampleAnalizeControl> samplescontrol = SampleAnalize.getSampleAnalizeControl(samples,year);
rdsc.Value = samplescontrol;
report.DataSources.Add(rdsc);
// detaliu
ReportDataSource rdscrot = new ReportDataSource();
rdscrot.Name = "SampleAnalizeCrotalia";
List<SampleAnalizeCrotalia> samplescrot = SampleAnalize.getSampleAnalizeCrotalia(fermaid, i,year);
rdscrot.Value = samplescrot;
report.DataSources.Add(rdscrot);
//controale
ReportDataSource rdsm = new ReportDataSource();
rdsm.Name = "SampleAnalizeExt";
List<SampleAnalizeExt> samplesext = SampleAnalize.GetSampleAnalizeExt(fermaid,year);
rdsm.Value = samplesext;
report.DataSources.Add(rdsm);
report.ReportPath = "ControlsSummaryAll.rdlc";
string datatestare = "Data: " + DateTime.Now.ToString("dd/MM/yyyy");
// set reports parameters
string title = userinfos.NumeFerma + " - " + "Cod exploatatie " + userinfos.UserCod;
ReportParameter pCod = new ReportParameter("CodExpl", userinfos.UserCod);
ReportParameter pData = new ReportParameter("DataExec", datatestare);
ReportParameter pChartTitle = new ReportParameter("ChartTitle", chartTitles[i]);
ReportParameter pChartLabelsName = new ReportParameter("ChartLabelsName", chartLabelsName[i]);
ReportParameter pUm = new ReportParameter("Um", um[i]);
ReportParameter pTitle = new ReportParameter("Title", title);
ReportParameter pUsername = new ReportParameter("UserName", username);
string baseurl = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + ConfigurationManager.AppSettings["baseurl"];
ReportParameter pBaseurl = new ReportParameter("BaseUrl", baseurl);
ReportParameter[] p = { pCod, pData, pChartTitle, pChartLabelsName, pUm,pTitle,pUsername,pBaseurl };
report.SetParameters(p);
report.Refresh();
// write xls
string httppath = ConfigurationManager.AppSettings["httppath"];
//"/Downloads/";
string filepath = ConfigurationManager.AppSettings["filepath"];
//"D:\\portal\\Downloads\\";
string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string fileid = chartTitles[i];
if (i == 8) fileid = fileid.Substring(0, 4);
string raport_excel = "Grafic" + fileid + "_" + userinfos.UserCod + "_" + (DateTime.Now).ToString("ddMMyyyy") + ".xls";
string excel_file = filepath + raport_excel;
string excel_link = path_raport_http + raport_excel;
byte[] xls_content = report.Render("EXCEL", deviceInfoXls, out mimeType, out encoding, out fileExtension, out streams, out warnings);
File.WriteAllBytes(excel_file, xls_content);
//rename sheets
using (StreamReader input = new StreamReader(excel_file))
{
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream));
if (workbook != null)
{
workbook.SetSheetName(0, chartTitles[i]);
// workbook.SetSheetName(1, "Detaliu date");
WriteExcel(excel_file, workbook);
}
}
//
HyperLink hyper = (HyperLink)holder.FindControl("xlslink" + parIds[i]);
hyper.Visible = true;
hyper.NavigateUrl = excel_link;
hyper.Text = raport_excel;
ErrorMessage.Text = "";
} //checked
}//for
}//samplecount >0
}//fermaid >0
else
{
// ErrorMessage.Text = "Nu exista date pentru codul "+ userinfos.UserCod;
}
}
public void TestEvaluateAll()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s1 = wb.CreateSheet();
NPOI.SS.UserModel.ISheet s2 = wb.CreateSheet();
wb.SetSheetName(0, "S1");
wb.SetSheetName(1, "S2");
IRow s1r1 = s1.CreateRow(0);
IRow s1r2 = s1.CreateRow(1);
IRow s2r1 = s2.CreateRow(0);
ICell s1r1c1 = s1r1.CreateCell(0);
ICell s1r1c2 = s1r1.CreateCell(1);
ICell s1r1c3 = s1r1.CreateCell(2);
s1r1c1.SetCellValue(22.3);
s1r1c2.SetCellValue(33.4);
s1r1c3.CellFormula = ("SUM(A1:B1)");
ICell s1r2c1 = s1r2.CreateCell(0);
ICell s1r2c2 = s1r2.CreateCell(1);
ICell s1r2c3 = s1r2.CreateCell(2);
s1r2c1.SetCellValue(-1.2);
s1r2c2.SetCellValue(-3.4);
s1r2c3.CellFormula = ("SUM(A2:B2)");
ICell s2r1c1 = s2r1.CreateCell(0);
s2r1c1.CellFormula = ("S1!A1");
// Not Evaluated yet
Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);
// Do a full Evaluate, as per our docs
// uses EvaluateFormulaCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
{
ICell c = (ICell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
// For Testing - all should be numeric
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, evaluator.EvaluateFormulaCell(c));
}
}
}
}
// Check now as expected
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
// Now do the alternate call, which zaps the formulas
// uses EvaluateInCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
{
ICell c = (ICell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateInCell(c);
}
}
}
}
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
}
public void TestEvaluateBooleanInCell_bug44508()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("1=1");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
try
{
fe.EvaluateInCell(cell);
}
catch (FormatException)
{
Assert.Fail("Identified bug 44508");
}
Assert.AreEqual(true, cell.BooleanCellValue);
}
private void btn_export_Click(object sender, EventArgs e)
{
FileStream xls = new FileStream(@"Template.xls", FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(xls);
#region sheet
ISheet sheet1 = wb.GetSheet("Sheet1");
string start = this.dateTimePicker1.Value.Date.ToShortDateString();
string end = this.dateTimePicker2.Value.Date.ToShortDateString();
wb.SetSheetName(0, start + "~" + end + @" ͼֽ������(��רҵ)");
string Obj = this.comboBox1.Text;//�������
if (Obj == string.Empty)
{
MessageBox.Show("��ѡ����������");
return;
}
string proj = this.comboBox2.Text;//��Ŀ
if (proj == string.Empty)
{
MessageBox.Show("��ѡ����Ŀ");
return;
}
DataSet mDSTotalDwg = GetDataSet(GetSQLs(proj, Obj, "ͼֽ����", start, end, true));
DataSet mDSPlanApp = GetDataSet(GetSQLs(proj, Obj, "�ƻ�����", start, end, true));
DataSet mDSActualApp = GetDataSet(GetSQLs(proj, Obj, "ʵ������", start, end, true));
DataSet mDSDelayApp = GetDataSet(GetSQLs(proj, Obj, "�ۼ���������", start, end, true));
DataSet mDSDelayNotApp = GetDataSet(GetSQLs(proj, Obj, "�ۼ�����δ����", start, end, true));
DataSet mDSRejectedDwg = GetDataSet(GetSQLs(proj, Obj, "���ܷ���", start, end, true));
DataSet mDSRejectedCDwg = GetDataSet(GetSQLs(proj, Obj, "������Ͽ�", start, end, true));
DataSet mDSPlannedAppDwg = GetDataSet(GetSQLs(proj, Obj, "�ƻ���ȫ�Ͽ�", start, end, true));
DataSet mDSApprovedDwg = GetDataSet(GetSQLs(proj, Obj, "��ȫ�Ͽ�", start, end, true));
IRow HeadRow = sheet1.GetRow(0);
IRow TotalDwg = sheet1.GetRow(1);
IRow PlanApp = sheet1.GetRow(2);
IRow ActualApp = sheet1.GetRow(3);
IRow DelayApp = sheet1.GetRow(4);
IRow DelayNotApp = sheet1.GetRow(5);
IRow RejectedDwg = sheet1.GetRow(6);
IRow RejectedCDwg = sheet1.GetRow(7);
IRow PlannedDwg = sheet1.GetRow(8);
IRow ApprovedDwg = sheet1.GetRow(9);
for (int i = 1; i < HeadRow.Cells.Count - 3; i++)
{
string discipline = HeadRow.Cells[i].ToString();
//ͼֽ����
for (int j = 0; j < mDSTotalDwg.Tables[0].Rows.Count; j++)
{
if (mDSTotalDwg.Tables[0].Rows[j][0].ToString() == discipline)
TotalDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSTotalDwg.Tables[0].Rows[j][1].ToString()));
}
//�ƻ�����
for (int j = 0; j < mDSPlanApp.Tables[0].Rows.Count; j++)
{
if (mDSPlanApp.Tables[0].Rows[j][0].ToString() == discipline)
PlanApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSPlanApp.Tables[0].Rows[j][1].ToString()));
}
//ʵ������
for (int j = 0; j < mDSActualApp.Tables[0].Rows.Count; j++)
{
if (mDSActualApp.Tables[0].Rows[j][0].ToString() == discipline)
ActualApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSActualApp.Tables[0].Rows[j][1].ToString()));
}
//�ۼ���������
for (int j = 0; j < mDSDelayApp.Tables[0].Rows.Count; j++)
{
if (mDSDelayApp.Tables[0].Rows[j][0].ToString() == discipline)
DelayApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSDelayApp.Tables[0].Rows[j][1].ToString()));
}
//�ۼ�����δ����
for (int j = 0; j < mDSDelayNotApp.Tables[0].Rows.Count; j++)
{
if (mDSDelayNotApp.Tables[0].Rows[j][0].ToString() == discipline)
DelayNotApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSDelayNotApp.Tables[0].Rows[j][1].ToString()));
}
//���ܷ���
for (int j = 0; j < mDSRejectedDwg.Tables[0].Rows.Count; j++)
{
if (mDSRejectedDwg.Tables[0].Rows[j][0].ToString() == discipline)
RejectedDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSRejectedDwg.Tables[0].Rows[j][1].ToString()));
}
//������Ͽ�
for (int j = 0; j < mDSRejectedCDwg.Tables[0].Rows.Count; j++)
{
if (mDSRejectedCDwg.Tables[0].Rows[j][0].ToString() == discipline)
RejectedCDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSRejectedCDwg.Tables[0].Rows[j][1].ToString()));
}
//�ƻ���ȫ�Ͽ�
for (int j = 0; j < mDSPlannedAppDwg.Tables[0].Rows.Count; j++)
{
if (mDSPlannedAppDwg.Tables[0].Rows[j][0].ToString() == discipline)
PlannedDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSPlannedAppDwg.Tables[0].Rows[j][1].ToString()));
}
//��ȫ�Ͽ�
for (int j = 0; j < mDSApprovedDwg.Tables[0].Rows.Count; j++)
{
if (mDSApprovedDwg.Tables[0].Rows[j][0].ToString() == discipline)
ApprovedDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSApprovedDwg.Tables[0].Rows[j][1].ToString()));
}
}
#endregion
try
{
FileStream file = new FileStream(@"Report.xls", FileMode.Create);
wb.Write(file);
file.Close();
MessageBox.Show("�������!");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
Report mRept = new Report();
/*
* DataSet mDSTotalDwg = GetDataSet(GetSQLs(proj, Obj, "ͼֽ����", start, end, true));
DataSet mDSPlanApp = GetDataSet(GetSQLs(proj, Obj, "�ƻ�����", start, end, true));
DataSet mDSActualApp = GetDataSet(GetSQLs(proj, Obj, "ʵ������", start, end, true));
DataSet mDSDelayApp = GetDataSet(GetSQLs(proj, Obj, "�ۼ���������", start, end, true));
DataSet mDSDelayNotApp = GetDataSet(GetSQLs(proj, Obj, "�ۼ�����δ����", start, end, true));
DataSet mDSRejectedDwg = GetDataSet(GetSQLs(proj, Obj, "���ܷ���", start, end, true));
DataSet mDSRejectedCDwg = GetDataSet(GetSQLs(proj, Obj, "������Ͽ�", start, end, true));
DataSet mDSPlannedAppDwg = GetDataSet(GetSQLs(proj, Obj, "�ƻ���ȫ�Ͽ�", start, end, true));
DataSet mDSApprovedDwg = GetDataSet(GetSQLs(proj, Obj, "��ȫ�Ͽ�", start, end, true));
*/
mRept.dataGridView1.DataSource = mDSTotalDwg.Tables[0];
mRept.dataGridView2.DataSource = mDSPlanApp.Tables[0];
mRept.dataGridView3.DataSource = mDSActualApp.Tables[0];
mRept.dataGridView4.DataSource = mDSDelayApp.Tables[0];
mRept.dataGridView5.DataSource = mDSDelayNotApp.Tables[0];
mRept.dataGridView6.DataSource = mDSRejectedDwg.Tables[0];
mRept.dataGridView7.DataSource = mDSRejectedCDwg.Tables[0];
mRept.dataGridView8.DataSource = mDSPlannedAppDwg.Tables[0];
mRept.dataGridView9.DataSource = mDSApprovedDwg.Tables[0];
mRept.Show();
}
public void TestParseStringLiterals_bug28754()
{
StringPtg sp;
try
{
sp = (StringPtg)ParseSingleToken("\"test\"\"ing\"", typeof(StringPtg));
}
catch (Exception e)
{
if (e.Message.StartsWith("Cannot Parse"))
{
throw new AssertionException("Identified bug 28754a");
}
throw e;
}
Assert.AreEqual("test\"ing", sp.Value);
HSSFWorkbook wb = new HSSFWorkbook();
try
{
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("right(\"test\"\"ing\", 3)");
String actualCellFormula = cell.CellFormula;
if ("RIGHT(\"test\"ing\",3)".Equals(actualCellFormula))
{
throw new AssertionException("Identified bug 28754b");
}
Assert.AreEqual("RIGHT(\"test\"\"ing\",3)", actualCellFormula);
}
finally
{
wb.Close();
}
}
public void TestSetFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
if ("SUM(A-32767:A-32766)".Equals(cell.CellFormula))
{
Assert.Fail("Identified bug 44539");
}
Assert.AreEqual("SUM(A32769:A32770)", cell.CellFormula);
wb.Close();
}
public void CreateReportExt()
{
LocalReport report = new LocalReport();
string mimeType, encoding, fileExtension;
string[] streams;
Warning[] warnings;
int year = Int32.Parse(Session["year"].ToString());
report.SubreportProcessing += new SubreportProcessingEventHandler(ItemsSubreportProcessingEventHandler);
string username = User.Identity.Name;
UserInfos userinfos = UserInfos.getUserInfos(username);
int fermaid = GetFermaId(userinfos.UserCod);
if (fermaid != 0)
{
string[] parIds = { "g", "p", "c", "l", "s", "h", "u", "n", "z" };
string[] chartLabelsName = { "Grasime (g/100g)", "Proteine (g/100g)", "Cazeina (g/100g)", "Lactoza (g/100g)", "SUN (g/100g)", "pH", "Uree (mg/dl)", "NCSx1000", "Cantitate lapte (l)" };
string[] chartTitles = { "Grasime", "Proteine", "Cazeina",
"Lactoza", "SUN", "Ph",
"Uree", "NCS","Cantitate lapte"};
string[] um = { "g/100g", "g/100g", "g/100g", "g/100g", "g/100g", "pH", "mg/dl", "NCSx1000", "l" };
//controale
ReportDataSource rdsm = new ReportDataSource();
rdsm.Name = "SampleAnalizeExt";
List<SampleAnalizeExt> samplesext = SampleAnalize.GetSampleAnalizeExt(fermaid,year);
rdsm.Value = samplesext;
report.DataSources.Clear();
report.DataSources.Add(rdsm);
report.ReportPath = "ControlsSummaryExt.rdlc";
string datatestare = "Data: " + DateTime.Now.ToString("dd/MM/yyyy");
// set reports parameters
string title = userinfos.NumeFerma + " - " + "Cod exploatatie " + userinfos.UserCod;
ReportParameter pCod = new ReportParameter("CodExpl", userinfos.UserCod);
ReportParameter pData = new ReportParameter("DataExec", datatestare);
// ReportParameter pChartTitle = new ReportParameter("ChartTitle", chartTitles[i]);
// ReportParameter pChartLabelsName = new ReportParameter("ChartLabelsName", chartLabelsName[i]);
// ReportParameter pUm = new ReportParameter("Um", um[i]);
ReportParameter pTitle = new ReportParameter("Title", title);
ReportParameter pUsername = new ReportParameter("UserName", username);
string baseurl = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + ConfigurationManager.AppSettings["baseurl"];
ReportParameter pBaseurl = new ReportParameter("BaseUrl", baseurl);
ReportParameter[] p = { pCod, pData,pTitle,pBaseurl,pUsername };
report.SetParameters(p);
report.Refresh();
// write xls
string httppath = ConfigurationManager.AppSettings["httppath"]+userinfos.AsocId+"/" + year +"/";
//"/Downloads/";
string filepath = ConfigurationManager.AppSettings["filepath"]+userinfos.AsocId+"\\" + year +"\\";
if (!Directory.Exists(filepath))
{
Directory.CreateDirectory(filepath);
}
//"D:\\portal\\Downloads\\";
string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string fileid = chartTitles[0];
string fermaname = replace_special_car_null(userinfos.NumeFerma);
string raport_excel = userinfos.UserCod + "_" + fermaname +".xls";
string excel_file = filepath + raport_excel;
string excel_link = path_raport_http + raport_excel;
byte[] xls_content = report.Render("EXCEL", deviceInfoXls, out mimeType, out encoding, out fileExtension, out streams, out warnings);
File.WriteAllBytes(excel_file, xls_content);
//rename sheets
List<SampleAnalize> samples = SampleAnalize.GetSampleAnalize(fermaid, "",year);
using (StreamReader input = new StreamReader(excel_file))
{
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream));
if (workbook != null && samples.Count >0)
{
for (int count=0; count < samples.Count; count++)
workbook.SetSheetName(count, samples[count].Nrcontrol);
for (int count = 0; count < chartTitles.Length; count++)
{
string sheetname = chartTitles[count].Replace(" ", "");
workbook.SetSheetName(count + samples.Count, "Grafic" + sheetname);
}
// workbook.SetSheetName(1, "Detaliu date");
WriteExcel(excel_file, workbook);
}
}
//
ContentPlaceHolder holder = (ContentPlaceHolder)Master.FindControl("MainContent");
HyperLink hyper = (HyperLink)holder.FindControl("xlslink" + parIds[0]);
hyper.Visible = true;
hyper.NavigateUrl = excel_link;
hyper.Text = raport_excel;
ErrorMessage.Text = "";
}//fermaid >0
else
{
// ErrorMessage.Text = "Nu exista date pentru codul "+ userinfos.UserCod;
}
}
public ActionResult Import(string ontologyCode)
{
if (string.IsNullOrEmpty(ontologyCode))
{
throw new ValidationException("未传入本体码");
}
OntologyDescriptor ontology;
if (!AcDomain.NodeHost.Ontologies.TryGetOntology(ontologyCode, out ontology))
{
throw new ValidationException("非法的本体码" + ontologyCode);
}
string message = "";
if (Request.Files.Count == 0)
{
throw new ValidationException("错误:请上传文件!");
}
HttpPostedFileBase file = Request.Files[0];
if (file == null)
{
throw new ValidationException("错误:请上传文件!");
}
string fileName = file.FileName;
if (string.IsNullOrEmpty(fileName) || file.ContentLength == 0)
{
message = "错误:请上传文件!";
}
else
{
bool isSave = true;
string fileType = fileName.Substring(fileName.LastIndexOf('.')).ToLower();
fileName = fileName.Substring(0, fileName.Length - fileType.Length);
if (file.ContentLength > 1024 * 1024 * 10)
{
message = "错误:文件大小不能超过10M!";
isSave = false;
}
else if (fileType != ".xls")
{
message = "错误:文件上传格式不正确,请上传.xls格式文件!";
isSave = false;
}
if (isSave)
{
string dirPath = Server.MapPath("~/Content/Import/Excel/" + ontology.Ontology.Code + "/" + AcSession.Account.Id);
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
string fullName = Path.Combine(dirPath, fileName + Guid.NewGuid().ToString() + fileType);
file.SaveAs(fullName);
int successSum = 0;
int failSum = 0;
try
{
FileStream fs = System.IO.File.OpenRead(fullName);
IWorkbook workbook = new HSSFWorkbook(fs);//从流内容创建Workbook对象
fs.Close();
ICellStyle failStyle = workbook.CreateCellStyle();
ICellStyle successStyle = workbook.CreateCellStyle();
failStyle.BorderBottom = BorderStyle.Thin;
failStyle.BorderLeft = BorderStyle.Thin;
failStyle.BorderRight = BorderStyle.Thin;
failStyle.BorderTop = BorderStyle.Thin;
failStyle.FillForegroundColor = HSSFColor.LightOrange.Index;
failStyle.FillPattern = FillPattern.SolidForeground;
successStyle.BorderBottom = BorderStyle.Thin;
successStyle.BorderLeft = BorderStyle.Thin;
successStyle.BorderRight = BorderStyle.Thin;
successStyle.BorderTop = BorderStyle.Thin;
successStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
successStyle.FillPattern = FillPattern.SolidForeground;
ISheet sheet = null;
// 工作表sheet的命名规则是：本体码 或 本体名 或 ‘工作表’
var sheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase) {
ontology.Ontology.Code, ontology.Ontology.Name, "工作表","Failed","失败的","Sheet1"
};
foreach (var sheetName in sheetNames)
{
if (sheet != null)
{
break;
}
int dataSheetIndex = workbook.GetSheetIndex(sheetName);
if (dataSheetIndex >= 0)
{
sheet = workbook.GetSheetAt(dataSheetIndex);
}
}
if (sheet == null)
{
System.IO.File.Delete(fullName);
throw new ValidationException("没有名称为'" + ontology.Ontology.Code + "'或'" + ontology.Ontology.Name + "'或'工作表'的sheet");
}
int sheetIndex = workbook.GetSheetIndex(sheet);
workbook.SetSheetName(sheetIndex, ResultSheetName);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
if (i != sheetIndex)
{
workbook.RemoveSheetAt(i);
}
}
if (sheet.LastRowNum == 2)
{
System.IO.File.Delete(fullName);
throw new ValidationException("没有待导入数据");
}
int rowIndex = 0;
IRow headRow1 = sheet.GetRow(rowIndex);
rowIndex++;
IRow headRow2 = sheet.GetRow(rowIndex);
rowIndex++;
IRow headRow3 = sheet.GetRow(rowIndex);
rowIndex++;
#region 提取列索引 这些字段在Excel模板上对应前缀为“$”的列。
int actionCodeIndex = -1,
localEntityIdIndex = -1,
descriptionIndex = -1,
eventReasonPhraseIndex = -1,
eventSourceTypeIndex = -1,
eventStateCodeIndex = -1,
eventSubjectCodeIndex = -1,
infoIdKeysIndex = -1,
infoValueKeysIndex = -1,
isDumbIndex = -1,
timeStampIndex = -1,
ontologyCodeIndex = -1,
reasonPhraseIndex = -1,
requestTypeIndex = -1,
serverTicksIndex = -1,
stateCodeIndex = -1,
versionIndex = -1;
string implicitMessageType = string.Empty,
implicitVerb = string.Empty,
implicitOntology = string.Empty,
implicitVersion = string.Empty,
implicitInfoIdKeys = string.Empty,
implicitInfoValueKeys = string.Empty;
bool implicitIsDumb = false;
for (int i = 0; i < headRow1.Cells.Count; i++)
{
string value = headRow1.GetCell(i).SafeToStringTrim();
string implicitValue = headRow2.GetCell(i).SafeToStringTrim();
if (value != null)
{
value = value.ToLower();
if (value == CommandColHeader.Verb.ToLower())
{
actionCodeIndex = i;
implicitVerb = implicitValue;
}
else if (value == CommandColHeader.LocalEntityId.ToLower())
{
localEntityIdIndex = i;
}
else if (value == CommandColHeader.Description.ToLower())
{
descriptionIndex = i;
}
else if (value == CommandColHeader.EventReasonPhrase.ToLower())
{
eventReasonPhraseIndex = i;
}
else if (value == CommandColHeader.EventSourceType.ToLower())
{
eventSourceTypeIndex = i;
}
else if (value == CommandColHeader.EventStateCode.ToLower())
{
eventStateCodeIndex = i;
}
else if (value == CommandColHeader.EventSubjectCode.ToLower())
{
eventSubjectCodeIndex = i;
}
else if (value == CommandColHeader.InfoIdKeys.ToLower())
{
infoIdKeysIndex = i;
implicitInfoIdKeys = implicitValue;
}
else if (value == CommandColHeader.InfoValueKeys.ToLower())
{
infoValueKeysIndex = i;
implicitInfoValueKeys = implicitValue;
}
else if (value == CommandColHeader.IsDumb.ToLower())
{
isDumbIndex = i;
bool isDumb;
if (!bool.TryParse(implicitValue, out isDumb))
{
System.IO.File.Delete(fullName);
throw new ApplicationException("IsDumb值设置不正确");
}
implicitIsDumb = isDumb;
}
else if (value == CommandColHeader.TimeStamp.ToLower())
{
timeStampIndex = i;
}
else if (value == CommandColHeader.Ontology.ToLower())
{
ontologyCodeIndex = i;
implicitOntology = implicitValue;
}
else if (value == CommandColHeader.ReasonPhrase.ToLower())
{
reasonPhraseIndex = i;
}
else if (value == CommandColHeader.MessageId.ToLower())
{
}
else if (value == CommandColHeader.MessageType.ToLower())
{
requestTypeIndex = i;
implicitMessageType = implicitValue;
}
else if (value == CommandColHeader.ServerTicks.ToLower())
{
serverTicksIndex = i;
}
else if (value == CommandColHeader.StateCode.ToLower())
{
stateCodeIndex = i;
}
else if (value == CommandColHeader.Version.ToLower())
{
versionIndex = i;
implicitVersion = implicitValue;
}
}
}
#endregion
int responsedSum = 0;
var commands = new Dictionary<int, Message>();
#region 检测Excel中的每一行是否合法
for (int i = rowIndex; i <= sheet.LastRowNum; i++)
{
// 检测合法性的进度，未展示进度条
var percent = (decimal)(((decimal)100 * i) / sheet.LastRowNum);
var row = sheet.GetRow(i);
if (row != null)
{
string infoIdKeys = row.GetCell(infoIdKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoIdKeys))
{
infoIdKeys = implicitInfoIdKeys;
}
string infoValueKeys = row.GetCell(infoValueKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoValueKeys))
{
infoValueKeys = implicitInfoValueKeys;
}
var infoIdCodes = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
var infoValueCodes = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
if (infoIdKeys != null)
{
foreach (var item in infoIdKeys.Split(','))
{
infoIdCodes.Add(item);
}
}
if (infoValueKeys != null)
{
foreach (var item in infoValueKeys.Split(','))
{
infoValueCodes.Add(item);
}
}
var infoId = new List<KeyValue>();
var infoValue = new List<KeyValue>();
for (int j = 0; j < headRow1.Cells.Count; j++)
{
var elementCode = headRow1.GetCell(j).SafeToStringTrim();
if (!string.IsNullOrEmpty(elementCode) && elementCode[0] != '$')
{
var value = row.GetCell(j).SafeToStringTrim();
if (infoIdCodes.Contains(elementCode))
{
infoId.Add(new KeyValue(elementCode, value));
}
if (infoValueCodes.Contains(elementCode))
{
infoValue.Add(new KeyValue(elementCode, value));
}
}
}
if (infoId.Count == 0 || infoId.All(a => string.IsNullOrEmpty(a.Value)))
{
continue;
}
bool isDumb;
string isDumbValue = row.GetCell(isDumbIndex).SafeToStringTrim();
if (!string.IsNullOrEmpty(isDumbValue))
{
if (!bool.TryParse(isDumbValue, out isDumb))
{
throw new ApplicationException("IsDumb值设置不正确");
}
}
else
{
isDumb = implicitIsDumb;
}
string actionCode = row.GetCell(actionCodeIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(actionCode))
{
actionCode = implicitVerb;
}
ontologyCode = row.GetCell(ontologyCodeIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(ontologyCode))
{
ontologyCode = implicitOntology;
}
var version = row.GetCell(versionIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(version))
{
version = implicitVersion;
}
var requestType = row.GetCell(requestTypeIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(requestType))
{
requestType = implicitMessageType;
}
int eventStateCode = 0;
if (!string.IsNullOrEmpty(row.GetCell(eventStateCodeIndex).SafeToStringTrim()))
{
if (!int.TryParse(row.GetCell(eventStateCodeIndex).SafeToStringTrim(), out eventStateCode))
{
throw new ApplicationException("eventStateCode值设置错误");
}
}
if (!string.IsNullOrEmpty(row.GetCell(timeStampIndex).SafeToStringTrim()))
{
long timeStamp = 0;
if (!long.TryParse(row.GetCell(timeStampIndex).SafeToStringTrim(), out timeStamp))
{
throw new ApplicationException("timeStamp值设置错误");
}
}
responsedSum++;
var ticks = DateTime.UtcNow.Ticks;
var command = new Message()
{
IsDumb = isDumb,
Verb = actionCode,
MessageId = Guid.NewGuid().ToString(),
Ontology = ontologyCode,
Version = version,
MessageType = requestType,
TimeStamp = DateTime.UtcNow.Ticks,
Body = new BodyData(infoId.ToArray(), infoValue.ToArray())
{
Event = new EventData
{
ReasonPhrase = row.GetCell(eventReasonPhraseIndex).SafeToStringTrim(),
SourceType = row.GetCell(eventSourceTypeIndex).SafeToStringTrim(),
Status = eventStateCode,
Subject = row.GetCell(eventSubjectCodeIndex).SafeToStringTrim()
}
}
};
var credential = new CredentialData
{
ClientType = ClientType.Node.ToName(),
CredentialType = CredentialType.Token.ToName(),
ClientId = AcDomain.NodeHost.Nodes.ThisNode.Node.Id.ToString(),
Ticks = ticks,
UserName = AcSession.Account.Id.ToString()
};
command.Credential = credential;
commands.Add(i, command);
}
}
if (responsedSum == 0)
{
throw new ValidationException("没有可导入行");
}
else
{
foreach (var command in commands)
{
// 检测合法性的进度，未展示进度条
var percent = (decimal)(((decimal)100 * command.Key) / commands.Count);
var result = AnyMessage.Create(HecpRequest.Create(AcDomain, command.Value), AcDomain.NodeHost.Nodes.ThisNode).Response();
if (result.Body.Event.Status < 200)
{
throw new ValidationException(string.Format("{0} {1} {2}", result.Body.Event.Status, result.Body.Event.ReasonPhrase, result.Body.Event.Description));
}
var row = sheet.GetRow(command.Key);
var stateCodeCell = row.CreateCell(stateCodeIndex);
var reasonPhraseCell = row.CreateCell(reasonPhraseIndex);
var descriptionCell = row.CreateCell(descriptionIndex);
var serverTicksCell = row.CreateCell(serverTicksIndex);
var localEntityIdCell = row.CreateCell(localEntityIdIndex);
if (result.Body.Event.Status < 200 || result.Body.Event.Status >= 300)
{
failSum++;
stateCodeCell.CellStyle = failStyle;
reasonPhraseCell.CellStyle = failStyle;
descriptionCell.CellStyle = failStyle;
}
else
{
stateCodeCell.CellStyle = successStyle;
reasonPhraseCell.CellStyle = successStyle;
descriptionCell.CellStyle = successStyle;
successSum++;
}
stateCodeCell.SetCellValue(result.Body.Event.Status);
reasonPhraseCell.SetCellValue(result.Body.Event.ReasonPhrase);
descriptionCell.SetCellValue(result.Body.Event.Description);
serverTicksCell.SetCellValue(DateTime.Now.ToString());
if (result.Body.InfoValue != null)
{
var idItem = result.Body.InfoValue.FirstOrDefault(a => a.Key.Equals("Id", StringComparison.OrdinalIgnoreCase));
if (idItem != null)
{
localEntityIdCell.SetCellValue(idItem.Value);
}
}
}
var newFile = new FileStream(fullName, FileMode.Create);
workbook.Write(newFile);
newFile.Close();
}
#endregion
}
catch (OfficeXmlFileException)
{
System.IO.File.Delete(fullName);
throw new ValidationException("暂不支持Office2007及以上版本的Excel文件");
}
}
}
TempData["Message"] = message;
return this.RedirectToAction("Import", new { ontologyCode });
}
public void CreateReportExt(int year)
{
LocalReport report = new LocalReport();
string mimeType, encoding, fileExtension;
string[] streams;
Warning[] warnings;
Session["year"] = year;
report.SubreportProcessing += new SubreportProcessingEventHandler(ItemsSubreportProcessingEventHandler);
int fermaid = 0;
try { fermaid = Convert.ToInt32(FermaId.Text); }
catch { fermaid = 0; }
string username = Username.Text;
UserInfos userinfos = UserInfos.getUserInfos(username);
if (fermaid != 0)
{
string[] parIds = { "g", "p", "c", "l", "s", "h", "u", "n", "z" };
string[] chartLabelsName = { "Grasime (g/100g)", "Proteine (g/100g)", "Cazeina (g/100g)", "Lactoza (g/100g)", "SUN (g/100g)", "pH", "Uree (mg/dl)", "NCSx1000", "Cantitate lapte (l)" };
string[] chartTitles = { "Grasime", "Proteine", "Cazeina",
"Lactoza", "SUN", "Ph",
"Uree", "NCS","Cantitate lapte"};
string[] um = { "g/100g", "g/100g", "g/100g", "g/100g", "g/100g", "pH", "mg/dl", "NCSx1000", "l" };
//controale
ReportDataSource rdsm = new ReportDataSource();
rdsm.Name = "SampleAnalizeExt";
logger.Info(string.Format("CreateReportExt|getting samples"));
List<SampleAnalizeExt> samplesext = SampleAnalize.GetSampleAnalizeExt(fermaid, year);
logger.Info(string.Format("CreateReportExt|got {0} samples", samplesext.Count));
rdsm.Value = samplesext;
report.DataSources.Clear();
report.DataSources.Add(rdsm);
report.ReportPath = "ControlsSummaryExt.rdlc";
string datatestare = "Data: " + DateTime.Now.ToString("dd/MM/yyyy");
// set reports parameters
string title = FermaName.Text + " - " + "Cod exploatatie " + FermaCod.Text;
ReportParameter pCod = new ReportParameter("CodExpl", FermaCod.Text);
ReportParameter pData = new ReportParameter("DataExec", datatestare);
// ReportParameter pChartTitle = new ReportParameter("ChartTitle", chartTitles[i]);
// ReportParameter pChartLabelsName = new ReportParameter("ChartLabelsName", chartLabelsName[i]);
// ReportParameter pUm = new ReportParameter("Um", um[i]);
ReportParameter pTitle = new ReportParameter("Title", title);
ReportParameter pUsername = new ReportParameter("UserName", username);
string baseurl = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + ConfigurationManager.AppSettings["baseurl"];
ReportParameter pBaseurl = new ReportParameter("BaseUrl", baseurl);
ReportParameter[] p = { pCod, pData, pTitle, pBaseurl, pUsername };
report.SetParameters(p);
report.Refresh();
// write xls
string httppath = ConfigurationManager.AppSettings["httppath"] + userinfos.AsocId + "/" + year +"/";
//"/Downloads/";
string filepath = ConfigurationManager.AppSettings["filepath"] + userinfos.AsocId + "\\"+ year +"\\";
if (!Directory.Exists(filepath))
{
Directory.CreateDirectory(filepath);
}
//"D:\\portal\\Downloads\\";
string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string fermaname = replace_special_car_null(FermaName.Text);
string raport_excel = FermaCod.Text + "_" + fermaname + ".xls";
string excel_file = filepath + raport_excel;
string excel_link = path_raport_http + raport_excel;
// infomess.Text = infomess.Text + ";" + excel_file;
byte[] xls_content = report.Render("EXCEL", StaticData.DEVICE_INFO_XLS, out mimeType, out encoding, out fileExtension, out streams, out warnings);
File.WriteAllBytes(excel_file, xls_content);
logger.Info(string.Format("CreateReportExt|wrote excel file {0}", excel_file));
//rename sheets
List<SampleAnalize> samples = SampleAnalize.GetSampleAnalize(fermaid, "",year);
using (StreamReader input = new StreamReader(excel_file))
{
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream));
if (workbook != null && samples.Count > 0)
{
for (int count = 0; count < samples.Count; count++)
workbook.SetSheetName(count, samples[count].Nrcontrol);
for (int count = 0; count < chartTitles.Length; count++)
{
string sheetname = chartTitles[count].Replace(" ", "");
workbook.SetSheetName(count + samples.Count, "Grafic" + sheetname);
}
// workbook.SetSheetName(1, "Detaliu date");
WriteExcel(excel_file, workbook);
}
}
}
}
