public int getRowCount(String sheetName)
{
int index = Workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return(0);
}
else
{
sheet = (HSSFSheet)Workbook.GetSheetAt(index);
int number = sheet.LastRowNum + 1;
return(number);
}
}
public void SetCellFormula(String formula)
{
if (IsPartOfArrayFormulaGroup)
{
NotifyArrayFormulaChanging();
}
int row = _record.Row;
int col = _record.Column;
short styleIndex = _record.XFIndex;
if (string.IsNullOrEmpty(formula))
{
NotifyFormulaChanging();
SetCellType(CellType.Blank, false, row, col, styleIndex);
return;
}
int sheetIndex = book.GetSheetIndex(_sheet);
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, book, FormulaType.Cell, sheetIndex);
SetCellType(CellType.Formula, false, row, col, styleIndex);
FormulaRecordAggregate agg = (FormulaRecordAggregate)_record;
FormulaRecord frec = agg.FormulaRecord;
frec.Options = ((short)2);
frec.Value = (0);
//only set to default if there is no extended format index already set
if (agg.XFIndex == (short)0)
{
agg.XFIndex = ((short)0x0f);
}
agg.SetParsedExpression(ptgs);
}
public void TestBug50298()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
ISheet sheet = wb.CloneSheet(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)");
wb.SetSheetName(wb.GetSheetIndex(sheet), "copy");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy");
wb.SetSheetOrder("copy", 0);
assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received");
wb.RemoveSheetAt(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
// check that the overall workbook serializes with its correct size
int expected = wb.Workbook.Size;
int written = wb.Workbook.Serialize(0, new byte[expected * 2]);
Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected);
HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb);
assertSheetOrder(read, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
}
/// <summary>
/// Export the files to CSV format from the file menu.
/// </summary>
/// <param name="sender">A reference to the control/object that raised the event.</param>
/// <param name="e">State information and event data associated with a routed event.</param>
private void Export_Click(object sender, RoutedEventArgs e)
{
if (ViewModel.Conflicts.Count != 0)
{
string message = "Exporting to Excel while there are conflicts may result in incorrect output. Do you wish to continue with the export?";
string caption = "Export to Excel";
MessageBoxImage icon = MessageBoxImage.Warning;
MessageBoxButton button = MessageBoxButton.YesNo;
MessageBoxResult result = System.Windows.MessageBox.Show(message, caption, button, icon);
if (result == MessageBoxResult.No)
{
return;
}
}
Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog();
saveFileDialog.Filter = "Excel Worksheets|*.xls";
if (saveFileDialog.ShowDialog() == true)
{
var fileName = saveFileDialog.FileName;
var templateFile = System.IO.Path.Combine(Environment.CurrentDirectory, "ClassroomGridTemplate.xls");
using (var fileStream = File.OpenRead(templateFile))
{
IWorkbook workbook = new HSSFWorkbook(fileStream);
workbook.RemoveSheetAt(workbook.GetSheetIndex("Sheet1"));
workbook.MissingCellPolicy = MissingCellPolicy.CREATE_NULL_AS_BLANK;
ExcelSchedulePrinter printer = new ExcelSchedulePrinter(fileName, workbook);
ICourseRepository courseRepository = CourseRepository.GetInstance();
new ScheduleVisualization(courseRepository, null, printer, "", null).PrintSchedule();
}
}
}
public static int getRowCount(String sheetName)
{
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return(0);
}
else
{
sheet = workbook.GetSheetAt(index);
Console.WriteLine("sheet 1" + sheet);
int number = sheet.LastRowNum + 1;
return(number);
}
}
public static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook,
CellRangeAddressList addressList, string formulaName)
{
var hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss");
ISheet CourseSheet = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true);
//CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("");
IRow row = null;
ICell cell = null;
for (int i = 0; i < datas.Length; i++)
{
row = CourseSheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(datas[i]);
}
IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length);
range.NameName = formulaName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(formulaName);
HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 增加一個新的表格，並將新增的表格作為活動表
/// </summary>
/// <param name="sheetName">表名稱</param>
public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
_currentSheet = _workbook.CloneSheet(i);
sheetIndex = _workbook.GetSheetIndex(_currentSheet);
_workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
_defaultExcelCellStyle = new ExcelCellStyle();
}
public void TestBug57231()
{
HSSFWorkbook wb = HSSFTestDataSamples
.OpenSampleWorkbook("57231_MixedGasReport.xls");
HSSFSheet sheet = wb.GetSheet("master") as HSSFSheet;
HSSFSheet newSheet = wb.CloneSheet(wb.GetSheetIndex(sheet)) as HSSFSheet;
int idx = wb.GetSheetIndex(newSheet);
wb.SetSheetName(idx, "newName");
// Write the output to a file
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbBack);
Assert.IsNotNull(wbBack.GetSheet("master"));
Assert.IsNotNull(wbBack.GetSheet("newName"));
}
public void TestBug58085RemoveSheetWithNames()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
ISheet sheet1 = wb1.CreateSheet("sheet1");
ISheet sheet2 = wb1.CreateSheet("sheet2");
ISheet sheet3 = wb1.CreateSheet("sheet3");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue("val1");
sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("val2");
sheet3.CreateRow(0).CreateCell((short)0).SetCellValue("val3");
IName namedCell1 = wb1.CreateName();
namedCell1.NameName = (/*setter*/ "name1");
String reference1 = "sheet1!$A$1";
namedCell1.RefersToFormula = (/*setter*/ reference1);
IName namedCell2 = wb1.CreateName();
namedCell2.NameName = (/*setter*/ "name2");
String reference2 = "sheet2!$A$1";
namedCell2.RefersToFormula = (/*setter*/ reference2);
IName namedCell3 = wb1.CreateName();
namedCell3.NameName = (/*setter*/ "name3");
String reference3 = "sheet3!$A$1";
namedCell3.RefersToFormula = (/*setter*/ reference3);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
IName nameCell = wb2.GetName("name1");
Assert.AreEqual("sheet1!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name2");
Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name3");
Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula);
wb2.RemoveSheetAt(wb2.GetSheetIndex("sheet1"));
nameCell = wb2.GetName("name1");
Assert.AreEqual("#REF!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name2");
Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name3");
Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula);
wb2.Close();
}
/// <summary>
/// 引用另一个工作表的形式 得到下拉
/// </summary>
/// <param name="book"></param>
/// <param name="columnIndex"></param>
/// <param name="values"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "")
{
if (values == null)
{
return;
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "_constraintSheet_";
}
//创建下拉数据到新表中
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);;
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = string.Empty;
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果无可选值的话，则增加一个空选项，防止用户填写内容
if (values.Count() == 0)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(" ");
rowIndex++;
}
//给该列所有单元格加上下拉选择
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN);
//创建验证
HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint);
//关联验证
HSSFSheet v = book.GetSheetAt(0) as HSSFSheet;
v.AddValidationData(valid);
}
public void TestPlainValueCache()
{
HSSFWorkbook wb = new HSSFWorkbook();
int numberOfSheets = 4098; // Bug 51448 reported that Evaluation Cache got messed up After 256 sheets
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = "SUM(A2:A" + (numberOfSheets + 2) + ")";
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = "new" + i + "!A1";
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
Assert.AreEqual(8394753.0, summaryCell.NumericCellValue);
}
public void TestGetSheetIndex()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet("Sheet2");
HSSFSheet sheet3 = (HSSFSheet)wb.CreateSheet("Sheet3");
HSSFSheet sheet4 = (HSSFSheet)wb.CreateSheet("Sheet4");
Assert.AreEqual(0, wb.GetSheetIndex(sheet1));
Assert.AreEqual(1, wb.GetSheetIndex(sheet2));
Assert.AreEqual(2, wb.GetSheetIndex(sheet3));
Assert.AreEqual(3, wb.GetSheetIndex(sheet4));
// remove sheets
wb.RemoveSheetAt(0);
wb.RemoveSheetAt(2);
// ensure that sheets are Moved up and Removed sheets are not found any more
Assert.AreEqual(-1, wb.GetSheetIndex(sheet1));
Assert.AreEqual(0, wb.GetSheetIndex(sheet2));
Assert.AreEqual(1, wb.GetSheetIndex(sheet3));
Assert.AreEqual(-1, wb.GetSheetIndex(sheet4));
}
public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values)
{
var sheetName = "_constraintSheet_";
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = "";
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果只有一个可选值，则增加一个相同的选项
if (values.Count() == 1)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(lastValue);
rowIndex++;
}
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), true);
return(new HSSFDataValidation(cellRegions, constraint));
}
public void SalvarDataTable(DataTable dt, string path)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//Criar uma tabela em branco
ISheet sheet = hssfworkbook.CreateSheet();
//Colocar dados nela
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
hssfworkbook.CreateSheet();
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), sheet.SheetName);
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
private static void TestPlainValueCache(HSSFWorkbook wb, int numberOfSheets)
{
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = ("SUM(A2:A" + (numberOfSheets + 2) + ")");
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = ("new" + i + "!A1");
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
}
private void Menu_Export(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel Worksheets|*.xls";
if (saveFileDialog.ShowDialog() == true)
{
var fileName = saveFileDialog.FileName;
var templateFile = Path.Combine(Environment.CurrentDirectory, "ClassroomGridTemplate.xls");
using (var fileStream = File.OpenRead(templateFile))
{
IWorkbook workbook = new HSSFWorkbook(fileStream);
workbook.RemoveSheetAt(workbook.GetSheetIndex("Sheet1"));
workbook.MissingCellPolicy = MissingCellPolicy.CREATE_NULL_AS_BLANK;
ExcelSchedulePrinter printer = new ExcelSchedulePrinter(fileName, workbook);
ICourseRepository courseRepository = InMemoryCourseRepository.getInstance();
//IRoomRepository roomRepository = InMemoryRoomRepository.getInstance();
new ScheduleVisualization(courseRepository, null, printer).PrintSchedule();
}
}
}
public ActionResult Generar(DateTime Desde, DateTime Hasta, Guid?Id_Parq)
{
try
{
Utilidades.Export export = new Utilidades.Export();
FileStream fs = new FileStream(Server.MapPath(@"~\Archivos\NPOI.xls"), FileMode.Open, FileAccess.Read);
Parqueadero parqueadero = db.Parqueaderoes.Find(Id_Parq);
// Getting the complete workbook...
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = parqueadero.NombreEmpresa_Parq; dsi.Manager = parqueadero.NombreEmpresa_Parq;
templateWorkbook.DocumentSummaryInformation = dsi;
// Getting the worksheet by its name...
HSSFSheet sheet = templateWorkbook.GetSheet("Hoja1") as HSSFSheet;
DataSet dts = new DataSet();
DataTable dtb = new DataTable();
dts = export.ConvertDataSet(reportes(Desde, Hasta, Id_Parq.Value).ToList(), dts);
if (dts.Tables.Count > 0)
{
dtb = dts.Tables[0];
}
int fila = 11, columna = 0, i = 0;
foreach (DataRow item in dtb.Rows)
{
HSSFRow dataRow = null;
IRow row = null;
dataRow = sheet.GetRow(fila) as HSSFRow;
if (dataRow == null)
{
row = sheet.CreateRow(fila);
dataRow = sheet.GetRow(fila) as HSSFRow;
if (dataRow == null)
{
throw new Exception("ha ocurrido un error al crear el archivo .xls");
}
}
//ArregloExcel(item);
foreach (var item1 in item.ItemArray)
{
if (i == 0)
{
if (row != null && row.Count() <= 27)
{
row.CreateCell(columna);
}
if (!string.IsNullOrEmpty(item1.ToString()))
{
dataRow.GetCell(columna).SetCellValue(string.Format(item1.ToString()));
}
else
{
dataRow.GetCell(columna).SetCellValue(string.Format("N/A"));
}
}
else
{
if (row != null && row.Count() <= 27)
{
row.CreateCell(columna);
}
if (!string.IsNullOrEmpty(item1.ToString()))
{
dataRow.GetCell(columna).SetCellValue(item1.ToString());
}
else
{
dataRow.GetCell(columna).SetCellValue(string.Format("N/A"));
}
}
// Setting the value 77 at row 5 column 1
columna++; i++;
}
fila++; columna = 0; i = 0;
}
//GraficaExcel(templateWorkbook, dtb);
MemoryStream ms = new MemoryStream();
string fechas = ("(" + Desde.ToString("yyyy-MM-dd") + "-" + Hasta.ToString("yyyy-MM-dd") + ")");
templateWorkbook.SetSheetName(templateWorkbook.GetSheetIndex(sheet), fechas);
// Writing the workbook content to the FileStream...
templateWorkbook.Write(ms);
// Sending the server processed data back to the user computer...
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Reportes" + fechas + ".xls")));
}
catch (Exception)
{
throw;
}
}
public void TestBackupRecord()
{
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < 10; i++)
{
HSSFSheet s = (HSSFSheet)wb.CreateSheet("Sheet " + i);
InternalSheet sheet = s.Sheet;
}
// Check the initial order
Assert.AreEqual(0, wb.GetSheetIndex("Sheet 0"));
Assert.AreEqual(1, wb.GetSheetIndex("Sheet 1"));
Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wb.GetSheetIndex("Sheet 3"));
Assert.AreEqual(4, wb.GetSheetIndex("Sheet 4"));
Assert.AreEqual(5, wb.GetSheetIndex("Sheet 5"));
Assert.AreEqual(6, wb.GetSheetIndex("Sheet 6"));
Assert.AreEqual(7, wb.GetSheetIndex("Sheet 7"));
Assert.AreEqual(8, wb.GetSheetIndex("Sheet 8"));
Assert.AreEqual(9, wb.GetSheetIndex("Sheet 9"));
// Change
wb.Workbook.SetSheetOrder("Sheet 6", 0);
wb.Workbook.SetSheetOrder("Sheet 3", 7);
wb.Workbook.SetSheetOrder("Sheet 1", 9);
// Check they're currently right
Assert.AreEqual(0, wb.GetSheetIndex("Sheet 6"));
Assert.AreEqual(1, wb.GetSheetIndex("Sheet 0"));
Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wb.GetSheetIndex("Sheet 4"));
Assert.AreEqual(4, wb.GetSheetIndex("Sheet 5"));
Assert.AreEqual(5, wb.GetSheetIndex("Sheet 7"));
Assert.AreEqual(6, wb.GetSheetIndex("Sheet 3"));
Assert.AreEqual(7, wb.GetSheetIndex("Sheet 8"));
Assert.AreEqual(8, wb.GetSheetIndex("Sheet 9"));
Assert.AreEqual(9, wb.GetSheetIndex("Sheet 1"));
// Read it in and see if it is correct.
MemoryStream baos = new MemoryStream();
wb.Write(baos);
MemoryStream bais = new MemoryStream(baos.ToArray());
HSSFWorkbook wbr = new HSSFWorkbook(bais);
Assert.AreEqual(0, wbr.GetSheetIndex("Sheet 6"));
Assert.AreEqual(1, wbr.GetSheetIndex("Sheet 0"));
Assert.AreEqual(2, wbr.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wbr.GetSheetIndex("Sheet 4"));
Assert.AreEqual(4, wbr.GetSheetIndex("Sheet 5"));
Assert.AreEqual(5, wbr.GetSheetIndex("Sheet 7"));
Assert.AreEqual(6, wbr.GetSheetIndex("Sheet 3"));
Assert.AreEqual(7, wbr.GetSheetIndex("Sheet 8"));
Assert.AreEqual(8, wbr.GetSheetIndex("Sheet 9"));
Assert.AreEqual(9, wbr.GetSheetIndex("Sheet 1"));
// Now get the index by the sheet, not the name
for (int i = 0; i < 10; i++)
{
NPOI.SS.UserModel.ISheet s = wbr.GetSheetAt(i);
Assert.AreEqual(i, wbr.GetSheetIndex(s));
}
}
/// <summary>
/// 读取excel2003
/// 当string.IsNullOrEmpty(sheetName)为TRUE时，则按sheetIndex读取
/// 否则，按sheetName读取
/// </summary>
/// <param name="Content"></param>
/// <returns></returns>
public DataTable GetExcelToTable03(Stream Content, string sheetName, int sheetIndex = 0) //2003 Excel
{
HSSFWorkbook workbook = new HSSFWorkbook(Content);
//获取excel的第一个sheet
HSSFSheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
}
else
{
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return(null);
}
sheet = (HSSFSheet)workbook.GetSheet(sheetName);
}
DataTable table = new DataTable();
try
{
//获取sheet的首行
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
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 + 1;
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
if (row == null || row.Cells.Count == 0)
{
continue;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
}
catch (Exception ex)
{
FileLog.WriteLog("Exception: " + ex);
table = null;
}
return(table);
}
/// <summary>
///從datatable中導出到excel
/// </summary>
/// <param name="strFileName"> excel文件名</param>
/// <param name="dtSource"> datatabe源數據</param>
/// <param name="strHeaderText">表名</param>
/// <param name="sheetnum"> sheet的編號</param>
/// <returns></returns>
static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary <string, string> dir, int sheetnum)
{
//創建工作簿和sheet
IWorkbook workbook = new HSSFWorkbook();
using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
{
if (writefile.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(writefile);
}
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(" yyyy-mm-dd ");
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表頭，填充列頭，樣式
if (rowIndex == 0)
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
#region 表頭及樣式
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
rowIndex = 1;
}
#endregion
#region 列頭及樣式
if (rowIndex == 1)
{
IRow headerRow = sheet.CreateRow(1); //第二行設置列名
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//寫入列標題
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
rowIndex = 2;
}
#endregion
}
#endregion
#region 填充內容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case " System.String ": //字符串類型
double result;
if (isNumeric(drValue, out result))
{
//數字字符串
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case " System.DateTime ": //日期類型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化顯示
break;
case " System.Boolean ": //布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case " System.Int16 ": //整型
case " System.Int32 ":
case " System.Int64 ":
case " System.Byte ":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case " System.Decimal ": //浮點型
case " System.Double ":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case " System.DBNull ": //空值處理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 生成excel
/// </summary>
/// <param name="templatePath">模板路径</param>
/// <param name="saveFolder">保存文件夹路径</param>
/// <param name="sheetName">工作薄名</param>
/// <param name="ds">数据源</param>
public static string ExecuteExcelExport(string templatePath, string saveFolder, string sheetName, DataSet ds)
{
try
{
HSSFWorkbook hssfworkbook = InitializeWorkbook(templatePath);
if (hssfworkbook == null)
{
return(null);
}
ISheet sheet1 = hssfworkbook.GetSheet(sheetName);
//删除其他工作薄
int sheetIndex = hssfworkbook.GetSheetIndex(sheet1);
if (hssfworkbook.NumberOfSheets > 1)
{
for (int i = hssfworkbook.NumberOfSheets - 1; i >= 0; i--)
{
if (i != sheetIndex)
{
hssfworkbook.RemoveSheetAt(i);
}
}
}
hssfworkbook.FirstVisibleTab = 0;
//相关参数
int columnHeaderIndex = GetGridRowIndex(sheet1, "行号");
if (columnHeaderIndex <= 0)
{
columnHeaderIndex = GetGridRowIndex(sheet1, "序号");
}
int gridRowCount = ds.Tables["Detail"].Rows.Count;
DataTable dtEnterprise = ds.Tables["Enterprise"];
DataTable dtMain = ds.Tables["Main"];
DataTable dtDetail = ds.Tables["Detail"];
//重新构造工作薄
CreateSheet(sheet1, columnHeaderIndex + 2, gridRowCount);
decimal totalMoney = 0; //总金额
decimal totalVolume = 0; //总体积
int totalBoxAmount = 0; //总箱数
int totalAmount = 0; //总数量
//企业信息
SetCellValueByTemplateStr(sheet1, "{$EnterpriseName}", dtEnterprise.Rows[0]["EnterpriseName"]);
SetCellValueByTemplateStr(sheet1, "{$EnterprisePhone}", dtEnterprise.Rows[0]["Telephone"]);
SetCellValueByTemplateStr(sheet1, "{$EnterpriseFax}", dtEnterprise.Rows[0]["Fax"]);
SetCellValueByTemplateStr(sheet1, "{$EnterpriseMobile}", dtEnterprise.Rows[0]["Mobile"]);
//数据
for (int i = 0; i < dtMain.Columns.Count; i++)
{
SetCellValueByTemplateStr(sheet1, "{$" + dtMain.Columns[i].ColumnName + "}", dtMain.Rows[0][i]);
}
//表明细
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
for (int j = 0; j < dtDetail.Columns.Count; j++)
{
SetCellValueByTemplateStr(sheet1, "{$" + dtDetail.Columns[j].ColumnName + "}", dtDetail.Rows[i][j], columnHeaderIndex + 1, gridRowCount, true);
}
totalMoney += DataTypeConvert.ToDecimal(dtDetail.Rows[i]["Money"]);
totalBoxAmount += DataTypeConvert.ToInt32(dtDetail.Rows[i]["BoxAmount"]);
totalAmount += DataTypeConvert.ToInt32(dtDetail.Rows[i]["Amount"]);
totalVolume += DataTypeConvert.ToDecimal(dtDetail.Rows[i]["OutVolumeDisplay"]);
}
//合计
SetCellValueByTemplateStr(sheet1, "{$TotalMoneyCH}", ConvertChIAmountHelper.ConvertSum(totalMoney + "")); //大写
SetCellValueByTemplateStr(sheet1, "{$TotalBoxAmount}", "共:" + totalBoxAmount + "箱"); //总箱数
SetCellValueByTemplateStr(sheet1, "{$TotalAmount}", totalAmount); //总数量
SetCellValueByTemplateStr(sheet1, "{$TotalMoney}", totalMoney); //总金额
SetCellValueByTemplateStr(sheet1, "{$TotalVolume}", totalVolume.ToString("f2")); //总体积
//生成文件
if (!System.IO.Directory.Exists(saveFolder))
{
System.IO.Directory.CreateDirectory(saveFolder);
}
string savePath = saveFolder + dtMain.Rows[0]["OrderCode"] + ".xls";
using (FileStream file = new FileStream(savePath, FileMode.Create))
{
hssfworkbook.Write(file);
file.Close();
}
return(dtMain.Rows[0]["OrderCode"] + ".xls");
}
catch (Exception e)
{
}
return("");
}
/// <summary>
/// 根据收货出账单号生成Execl
/// </summary>
/// <param name="bill"></param>
/// <param name="list"></param>
public static void ExeclCreate(ReceivingBill bill, List <BillModel> list)
{
Log.Info("LMS开始生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
var groupShipping = list.GroupBy(p => p.InShippingMethodName).Select(g => new
{
ShippingMethodName = g.Key,
TotalFee = g.Sum(p => p.Freight + p.FuelCharge +
p.Register + p.Surcharge +
p.TariffPrepayFee +
p.SpecialFee + p.RemoteAreaFee)
}).ToList();
using (
var file = new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate/FinancialCreditBill.xls", FileMode.Open,
FileAccess.Read))
{
IWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheetNew = hssfworkbook.CloneSheet(0);
sheetNew.GetRow(1).GetCell(0).SetCellValue("客户名称：{0}".FormatWith(bill.CustomerName));
sheetNew.GetRow(1).GetCell(1).SetCellValue("账单结算期间：{0}至{1}".FormatWith(DateTime.Parse(bill.BillStartTime).ToString("yyyy-MM-dd"), DateTime.Parse(bill.BillEndTime).ToString("yyyy-MM-dd")));
IRow sourceRow = sheetNew.GetRow(2);
int startRow = 2;
foreach (var t in groupShipping)
{
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow row = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref row);
row.GetCell(0).SetCellValue(t.ShippingMethodName);
row.GetCell(1).SetCellValue("总费用：{0}".FormatWith(t.TotalFee.ToString("F2")));
}
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow srow = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref srow);
srow.GetCell(0).SetCellValue("结算人：{0}".FormatWith(bill.ReceivingBillAuditor));
startRow = startRow + 3;
int startR = startRow;
foreach (var b in list)
{
IRow row = sheetNew.CreateRow(startRow);
row.CreateCell(0).SetCellValue(b.WayBillNumber);
row.CreateCell(1).SetCellValue(b.CustomerOrderNumber ?? "");
row.CreateCell(2).SetCellValue(b.CreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(3).SetCellValue(b.InStorageCreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(4).SetCellValue(b.TrackingNumber ?? "");
row.CreateCell(5).SetCellValue(b.ChineseName);
row.CreateCell(6).SetCellValue(b.InShippingMethodName);
row.CreateCell(7).SetCellValue(b.SettleWeight.ToString("F4"));
row.CreateCell(8).SetCellValue(b.Weight.ToString("F4"));
row.CreateCell(9).SetCellValue(b.CountNumber);
row.CreateCell(10).SetCellValue(double.Parse(b.Freight.ToString("F2")));
row.CreateCell(11).SetCellValue(double.Parse(b.Register.ToString("F2")));
row.CreateCell(12).SetCellValue(double.Parse(b.FuelCharge.ToString("F2")));
row.CreateCell(13).SetCellValue(double.Parse(b.Surcharge.ToString("F2")));
row.CreateCell(14).SetCellValue(double.Parse(b.TariffPrepayFee.ToString("F2")));
row.CreateCell(15).SetCellValue(double.Parse(b.SpecialFee.ToString("F2")));
row.CreateCell(16).SetCellValue(double.Parse(b.RemoteAreaFee.ToString("F2")));
startRow++;
row.CreateCell(17).SetCellFormula("sum(K" + startRow + ":Q" + startRow + ")");
}
IRow totalRow = sheetNew.CreateRow(startRow);
for (int i = 0; i < 18; i++)
{
totalRow.CreateCell(i).SetCellValue("");
}
startR++;
totalRow.Cells[9].SetCellValue("各项计费小计：");
totalRow.Cells[10].SetCellFormula("sum(K" + startR + ":K" + startRow + ")");
totalRow.Cells[11].SetCellFormula("sum(L" + startR + ":L" + startRow + ")");
totalRow.Cells[12].SetCellFormula("sum(M" + startR + ":M" + startRow + ")");
totalRow.Cells[13].SetCellFormula("sum(N" + startR + ":N" + startRow + ")");
totalRow.Cells[14].SetCellFormula("sum(O" + startR + ":O" + startRow + ")");
totalRow.Cells[15].SetCellFormula("sum(P" + startR + ":P" + startRow + ")");
totalRow.Cells[16].SetCellFormula("sum(Q" + startR + ":Q" + startRow + ")");
totalRow.Cells[17].SetCellFormula("sum(R" + startR + ":R" + startRow + ")");
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheetNew), bill.ReceivingBillID);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
if (File.Exists(Path.Combine(path, bill.ReceivingBillID + ".xls")))
{
File.Delete(Path.Combine(path, bill.ReceivingBillID + ".xls"));
}
var savefile = new FileStream(Path.Combine(path, bill.ReceivingBillID + ".xls"), FileMode.Create);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.Write(savefile);
file.Close();
}
Log.Info("LMS完成生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
}
/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="file">导出路径(包括文件名与扩展名)</param>
/// <param name="dts"></param>
/// <param name="onSheetBeforeAction"></param>
/// <param name="onSheetAction"></param>
public static void ExportDataTableToExcel(string file, List <DataTable> dts,
Func <DataTable, DataTable> onSheetBeforeAction = null,
Func <ISheet, DataTable, DataTable> onSheetAction = null)
{
IWorkbook workbook = null;
string fileExt = Path.GetExtension(file).ToLower();
switch (fileExt)
{
case ".xlsx":
case ".xlsm":
workbook = new XSSFWorkbook();
break;
case ".xls":
workbook = new HSSFWorkbook();
break;
default:
throw new Exception("保存的文件格式不符合要求 ！");
}
foreach (DataTable dt in dts)
{
if (fileExt == ".xls" && dt.Rows.Count > 65536)
{
throw new Exception("数据量过大，请保存为.xlsx");
}
if (onSheetBeforeAction != null)
{
if (onSheetBeforeAction.Invoke(dt) == null)
{
continue;
}
}
ISheet sheet = string.IsNullOrEmpty(dt.TableName)
? workbook.CreateSheet("Sheet1")
: workbook.CreateSheet(dt.TableName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
var value = dt.Columns[i].ColumnName;
cell.SetCellValue(value);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
var value = dt.Rows[i][j].ToString();
cell.SetCellValue(value);
}
}
//默认一个样式以及自动宽度
for (int i = 0; i < dt.Columns.Count; i++)
{
var style = workbook.CreateCellStyle();
sheet.SetDefaultColumnStyle(i, style);
sheet.AutoSizeColumn(i);
}
if (onSheetAction != null)
{
if (onSheetAction.Invoke(sheet, dt) == null)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheet));
}
}
//string regex = "(([\u4E00-\u9FA5])|([\u30A0-\u30FF])";
//var list = ConvertDataTableToRowsList(dt).Select(p => string.Join("|", p.Cast<string>().ToArray())).Select(p => Regex.IsMatch(p, regex)).ToList();
//for (int i = 0; i < list.Count; i++)
//{
// var show = list[i];
// sheet.SetColumnHidden(i, !show);
// //if (show)
// // sheet.SetColumnWidth(0, 200*256 + 200);
//}
}
}
private Ptg ParseIdentifier(String name)
{
if (look == ':' || look == '.')
{ // this Is a AreaReference
GetChar();
while (look == '.')
{ // formulas can have . or .. or ... instead of :
GetChar();
}
String first = name;
String second = Name;
return(new AreaPtg(first + ":" + second));
}
if (look == '!')
{
Match('!');
String sheetName = name;
String first = Name;
short externIdx = book.GetExternalSheetIndex(book.GetSheetIndex(sheetName));
if (look == ':')
{
Match(':');
String second = Name;
if (look == '!')
{
//The sheet name was included in both of the areas. Only really
//need it once
Match('!');
String third = Name;
if (!sheetName.Equals(second))
{
throw new Exception("Unhandled double sheet reference.");
}
return(new Area3DPtg(first + ":" + third, externIdx));
}
return(new Area3DPtg(first + ":" + second, externIdx));
}
return(new Ref3DPtg(first, externIdx));
}
if (name.Equals("TRUE", StringComparison.InvariantCultureIgnoreCase) ||
name.Equals("FALSE", StringComparison.InvariantCultureIgnoreCase))
{
return(new BoolPtg(name.ToUpper()));
}
// This can be either a cell ref or a named range
// Try To spot which it Is
Regex regex = new Regex(CELL_REFERENCE_PATTERN);
bool cellRef = regex.IsMatch(name);
if (cellRef)
{
return(new RefPtg(name));
}
for (int i = 0; i < book.NumberOfNames; i++)
{
// named range name matching Is case insensitive
if (book.GetNameAt(i).NameName.ToLower().Equals(name.ToLower()))
{
return(new NamePtg(name, book));
}
}
throw new FormulaParseException("Found reference To named range \""
+ name + "\", but that named range wasn't defined!");
}
/// <summary>
/// 设置打印区域
/// </summary>
/// <param name="firstRowIndex"></param>
/// <param name="lastRowIndex"></param>
/// <param name="firstColumnIndex"></param>
/// <param name="lastColumnIndex"></param>
public void SetPrintArea(int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex)
{
this._workbook.SetPrintArea(_workbook.GetSheetIndex(_currentSheet), firstColumnIndex, lastColumnIndex, firstRowIndex, lastRowIndex);
}
/**
* @return human Readable string for debug purposes
*/
public String FormatAsString()
{
return("R=" + _srcRowNum + " C=" + _srcColNum + " ShIx=" + _workbook.GetSheetIndex(_sheet));
}
public int GetExternalSheetIndex(String sheetName)
{
int sheetIndex = _uBook.GetSheetIndex(sheetName);
return(_iBook.CheckExternSheet(sheetIndex));
}
/// <summary>
/// 带封面模板导出excel报告
/// </summary>
/// <param name="dtTask"></param>
/// <param name="mediaType"></param>
/// <param name="adProductName"></param>
/// <param name="planBegin"></param>
/// <param name="planEnd"></param>
/// <returns></returns>
private MemoryStream CreatWebReportForFrame(DataTable dtTask, string mediaType, string adProductName, string scheduleName, DateTime planBegin, DateTime planEnd)
{
FileStream fileStream = null;
string configPath = HttpContext.Current.Server.MapPath("~/xlsTemplate/");
fileStream = new FileStream(configPath + "ReportFrame.xls", FileMode.Open);
hssfworkbook = new HSSFWorkbook(fileStream);
int total = monitor.GetTaskPlanListCount(GetCondition("export"));
if (hssfworkbook != null)
{
#region 处理封面
sheet = hssfworkbook.GetSheetAt(0);
row = sheet.GetRow(14);
cell = row.GetCell(0);
cell.SetCellValue(adProductName + planBegin.ToString("yyyy年M月") + mediaType + "监测");
row = sheet.GetRow(40);
cell = row.GetCell(0);
cell.SetCellValue(planBegin.ToString("yyyy年M月d日") + "至" + planEnd.ToString("yyyy年M月d日"));
#endregion
#region 处理内容
ICellStyle normalStyle = NormalStyle;
ICellStyle redStyle = RedStyle;
ICellStyle cityStyle = CityStyle;
ICellStyle blockStyle = BlockStyle;
sheet = hssfworkbook.GetSheetAt(1);
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), (string.IsNullOrEmpty(scheduleName) ? adProductName : scheduleName));
row = sheet.GetRow(4);
cell = row.GetCell(0);
cell.SetCellValue("排期名称：" + adProductName);
cell = row.GetCell(2);
cell.SetCellValue("投放时间：" + ConvertHelper.GetShortDateString(planBegin) + "至" + ConvertHelper.GetShortDateString(planEnd));
row = sheet.GetRow(5);
cell = row.GetCell(0);
cell.SetCellValue("全部投放量：" + total);
cell = row.GetCell(1);
cell.SetCellValue("抽检量：" + dtTask.Select(" Status=3").Length);
cell = row.GetCell(2);
cell.SetCellValue("抽检率：" + Math.Round((Convert.ToDouble(dtTask.Select(" Status=3").Length) / Convert.ToDouble(total) * 100), 2) + "%");
row = sheet.GetRow(6);
cell = row.GetCell(0);
cell.SetCellValue("抽检量：" + dtTask.Select(" Status=3").Length);
cell = row.GetCell(1);
cell.SetCellValue("合格量：" + dtTask.Select(" Status=3 AND AbnormalType in (0,5)").Length);
cell = row.GetCell(2);
cell.SetCellValue("不合格量：" + dtTask.Select(" Status=3 AND AbnormalType in (1,2,3,4,6,7)").Length);
cell = row.GetCell(3);
cell.SetCellValue("合格率：" + Math.Round((Convert.ToDouble(dtTask.Select(" Status=3 AND AbnormalType in (0,5)").Length) / Convert.ToDouble(dtTask.Select(" Status=3").Length) * 100), 2) + "%");
row = sheet.GetRow(7);
cell = row.GetCell(0);
cell.SetCellValue("异常原因：");
cell = row.GetCell(1);
cell.SetCellValue("未上画：" + dtTask.Select(" Status=3 AND AbnormalType=3").Length);
cell = row.GetCell(2);
cell.SetCellValue("遮挡：" + dtTask.Select(" Status=3 AND AbnormalType=6").Length);
cell = row.GetCell(3);
cell.SetCellValue("破损：" + dtTask.Select(" Status=3 AND AbnormalType=4").Length);
row = sheet.CreateRow(10);
cell = row.CreateCell(0);
cell.CellStyle = TitleStyle;
cell.SetCellValue("城市");
cell = row.CreateCell(1);
cell.CellStyle = TitleStyle;
cell.SetCellValue("楼宇名称");
cell.CellStyle = TitleStyle;
cell = row.CreateCell(2);
cell.CellStyle = TitleStyle;
cell.SetCellValue("未上画");
cell = row.CreateCell(3);
cell.CellStyle = TitleStyle;
cell.SetCellValue("遮挡");
cell = row.CreateCell(4);
cell.CellStyle = TitleStyle;
cell.SetCellValue("破损");
cell = row.CreateCell(5);
cell.CellStyle = TitleStyle;
cell.SetCellValue("正常");
cell = row.CreateCell(6);
cell.CellStyle = TitleStyle;
cell.SetCellValue("合计");
int notPaintTotal = 0, hiddenTotal = 0, normalTotal = 0, breakTotal = 0;
int tempNum = 11;
DataTable dt = monitor.GetReportDetailList(GetCondition(), 1, 9999).Tables[0];
DataTable dtCity = monitor.GetCityReportDetailList(GetCondition()).Tables[0];
DataTable dtNew = new DataTable();
dtNew.Columns.Add(new DataColumn("RegionId", typeof(string)));
dtNew.Columns.Add(new DataColumn("City", typeof(string)));
dtNew.Columns.Add(new DataColumn("RegionName", typeof(string)));
dtNew.Columns.Add(new DataColumn("BlockName", typeof(string)));
dtNew.Columns.Add(new DataColumn("NotPaintCount", typeof(int)));
dtNew.Columns.Add(new DataColumn("HiddenCount", typeof(int)));
dtNew.Columns.Add(new DataColumn("BreakCount", typeof(int)));
dtNew.Columns.Add(new DataColumn("NormalCount", typeof(int)));
for (int i = 0; i < dtCity.Rows.Count; i++)
{
DataRow dr = dtNew.NewRow();
dr["RegionId"] = dtCity.Rows[i]["RegionId"];
dr["City"] = dtCity.Rows[i]["RegionName"];
dr["RegionName"] = dtCity.Rows[i]["RegionName"] + " Total";
dr["BlockName"] = "";
dr["NotPaintCount"] = dtCity.Rows[i]["NotPaintCount"];
dr["HiddenCount"] = dtCity.Rows[i]["HiddenCount"];
dr["BreakCount"] = dtCity.Rows[i]["BreakCount"];
dr["NormalCount"] = dtCity.Rows[i]["NormalCount"];
dtNew.Rows.Add(dr);
DataRow[] drc = dt.Select(" RegionId='" + dtCity.Rows[i]["RegionId"] + "'");
for (int j = 0; j < drc.Length; j++)
{
dr = dtNew.NewRow();
dr["RegionId"] = drc[j]["RegionId"];
dr["City"] = drc[j]["RegionName"];
dr["RegionName"] = j == 0 ? drc[j]["RegionName"] : "";
dr["BlockName"] = drc[j]["BlockName"];
dr["NotPaintCount"] = drc[j]["NotPaintCount"];
dr["HiddenCount"] = drc[j]["HiddenCount"];
dr["BreakCount"] = drc[j]["BreakCount"];
dr["NormalCount"] = drc[j]["NormalCount"];
dtNew.Rows.Add(dr);
}
notPaintTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["NotPaintCount"]);
hiddenTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["HiddenCount"]);
normalTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["NormalCount"]);
breakTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["BreakCount"]);
}
for (int j = 0; j < dtNew.Rows.Count; j++)
{
bool IsCityTr = dtNew.Rows[j]["RegionName"].ToString().Contains("Total");
row = sheet.CreateRow(tempNum);
cell = row.CreateCell(0);
cell.CellStyle = IsCityTr ? cityStyle : blockStyle;
cell.SetCellValue(dtNew.Rows[j]["RegionName"].ToString());
cell = row.CreateCell(1);
cell.CellStyle = IsCityTr ? cityStyle : blockStyle;
cell.CellStyle.ShrinkToFit = true;
cell.SetCellValue(dtNew.Rows[j]["BlockName"].ToString());
cell = row.CreateCell(2);
cell.CellStyle = dtNew.Rows[j]["NotPaintCount"].ToString() == "0" ? (IsCityTr ? cityStyle : blockStyle) : (IsCityTr ? cityStyle : AbnormalStyle);
cell.SetCellValue(dtNew.Rows[j]["NotPaintCount"].ToString() == "0" ? "" : dtNew.Rows[j]["NotPaintCount"].ToString());
cell = row.CreateCell(3);
cell.CellStyle = dtNew.Rows[j]["HiddenCount"].ToString() == "0" ? (IsCityTr ? cityStyle : blockStyle) : (IsCityTr ? cityStyle : AbnormalStyle);
cell.SetCellValue(dtNew.Rows[j]["HiddenCount"].ToString() == "0" ? "" : dtNew.Rows[j]["HiddenCount"].ToString());
cell = row.CreateCell(4);
cell.CellStyle = dtNew.Rows[j]["BreakCount"].ToString() == "0" ? (IsCityTr ? cityStyle : blockStyle) : (IsCityTr ? cityStyle : AbnormalStyle);
cell.SetCellValue(dtNew.Rows[j]["BreakCount"].ToString() == "0" ? "" : dtNew.Rows[j]["BreakCount"].ToString());
cell = row.CreateCell(5);
cell.CellStyle = IsCityTr ? cityStyle : blockStyle;
cell.SetCellValue(dtNew.Rows[j]["NormalCount"].ToString() == "0" ? "" : dtNew.Rows[j]["NormalCount"].ToString());
cell = row.CreateCell(6);
cell.CellStyle = IsCityTr ? cityStyle : blockStyle;
cell.SetCellValue((ConvertHelper.GetInteger(dtNew.Rows[j]["NotPaintCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["HiddenCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["BreakCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["NormalCount"])).ToString() == "0" ? "" : (ConvertHelper.GetInteger(dtNew.Rows[j]["NotPaintCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["HiddenCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["BreakCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["NormalCount"])).ToString());
tempNum++;
}
row = sheet.CreateRow(tempNum);
cell = row.CreateCell(0);
cell.CellStyle = blockStyle;
cell.SetCellValue("合计");
cell = row.CreateCell(1);
cell.CellStyle = blockStyle;
cell.SetCellValue("");
cell = row.CreateCell(2);
cell.CellStyle = blockStyle;
cell.SetCellValue(notPaintTotal);
cell = row.CreateCell(3);
cell.CellStyle = blockStyle;
cell.SetCellValue(hiddenTotal);
cell = row.CreateCell(4);
cell.CellStyle = blockStyle;
cell.SetCellValue(breakTotal);
cell = row.CreateCell(5);
cell.CellStyle = blockStyle;
cell.SetCellValue(normalTotal);
cell = row.CreateCell(6);
cell.CellStyle = blockStyle;
cell.SetCellValue(notPaintTotal + hiddenTotal + breakTotal + normalTotal);
sheet = hssfworkbook.GetSheetAt(2);
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), "监测明细");
int rowNum = 1;
for (int i = 0; i < dtTask.Rows.Count; i++)
{
row = sheet.CreateRow(rowNum);
cell = row.CreateCell(0);
cell.CellStyle = normalStyle;
cell.SetCellValue(i + 1);//序号
cell = row.CreateCell(1);
cell.CellStyle = normalStyle;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["RegionName"]));
cell = row.CreateCell(2);
cell.CellStyle = normalStyle;
cell.CellStyle.ShrinkToFit = true;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["StreetAddress"]));
cell = row.CreateCell(3);
cell.CellStyle = normalStyle;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["BlockName"]));
cell = row.CreateCell(4);
cell.CellStyle = normalStyle;
cell.CellStyle.ShrinkToFit = true;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["PointName"]));
cell = row.CreateCell(5);
cell.CellStyle = normalStyle;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["MediaType"]));
cell = row.CreateCell(6);
cell.CellStyle = normalStyle;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["AdProductName"]));
cell = row.CreateCell(7);
cell.CellStyle = normalStyle;
cell.SetCellValue(ConvertHelper.GetDateTimeString(dtTask.Rows[i]["BeginDate"], "yyyy.MM.dd") + "-" + ConvertHelper.GetDateTimeString(dtTask.Rows[i]["EndDate"], "yyyy.MM.dd"));
cell = row.CreateCell(8);
cell.CellStyle = (ConvertHelper.GetInteger(dtTask.Rows[i]["AbnormalType"]) == 0 || ConvertHelper.GetInteger(dtTask.Rows[i]["AbnormalType"]) == 5 ? normalStyle : redStyle);
cell.SetCellValue(GetTaskPlanStatus(ConvertHelper.GetString(dtTask.Rows[i]["Status"]), ConvertHelper.GetString(dtTask.Rows[i]["AbnormalType"]), "export"));
cell = row.CreateCell(9);
cell.CellStyle = normalStyle;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["AuditReason"]));
cell = row.CreateCell(10);
cell.CellStyle = normalStyle;
cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["SpareOne"]));
rowNum++;
}
}
#endregion
if (fileStream != null)
{
fileStream.Close();
}
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
return(ms);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="startIndex">读取标头开始行,下标0开始</param>
/// <param name="sheetList">sheet集合,如sheet1,sheet2,sheet3</param>
/// <param name="errorMessage">返回的异常数据</param>
/// <returns></returns>
public static List <DataTable> GetListByImport(string strFileName, int startIndex, List <string> sheetName, ref string errorMessage)
{
List <DataTable> tableList = new List <DataTable>();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
for (int i = 0; i < sheetName.Count; i++)
{
try
{
if (hssfworkbook.GetSheetIndex(sheetName[i]) != -1)
{
}
else
{
errorMessage = "上传的EXCEL不规范！";
return(null);
}
}
catch
{
errorMessage = sheetName[i] + "不存在，请不要随意修改EXCEL！";
return(null);
}
}
for (int sheetIndex = 0; sheetIndex < hssfworkbook.NumberOfSheets; sheetIndex++)
{
ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
DataTable dt = new DataTable(hssfworkbook.GetSheetName(sheetIndex));
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(startIndex);
int cellCount = 0;
try
{
cellCount = headerRow.LastCellNum;
}
catch
{
tableList.Add(dt);
continue;
}
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
try
{
dt.Columns.Add(cell.ToString());
}
catch
{
cellCount = j;
break;
}
}
for (int i = (sheet.FirstRowNum + 1 + startIndex); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
try
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).ToString().Trim().Replace(" ", "").Length == 0)
{
dataRow[j] = "";
//break;
//return dt;
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim().Replace(" ", "");
}
}
//else
//{
// return dt;
//}
}
}
catch
{
break;
}
dt.Rows.Add(dataRow);
}
tableList.Add(dt);
}
return(tableList);
}
public static MemoryStream ToMemoryStram(SummaryInfo summaryInfo,
List <Matrix <InspectionDataReport> > list_InspectionDataReportMatrix,
List <DefectTypeInfoReport> list_DefectTypeInfoReport,
DefectStatistics defectStatistics,
int ReportModel)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(ExcelText.SheetName_SummaryOperator);
#region SummaryOperator
int rowIndex = 0;
int code2drowIndex = 0;
//1、产品编号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode);
//2、批次号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot);
//3、设备号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine);
//4、操作员
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator);
//5、开始日期
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartDate, summaryInfo.StartDate);
//6、开始时间
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartTime, summaryInfo.StartTime);
//7、结束日期
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndDate, summaryInfo.EndDate);
//8、结束时间
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndTime, summaryInfo.EndTime);
rowIndex++;
//10、批次理论盘数 没有输入默认10 已屏蔽
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.TotalNumberOfStrips, summaryInfo.TotalNumberOfStrips.ToString());
//11、已检测的盘数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsInspected, summaryInfo.NumberOfStripsInspected.ToString());
//12、未检测的盘数 已屏蔽
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsNotInspected, summaryInfo.NumberOfStripsNotInspected.ToString());
//13、单盘芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.QuantityOfDevicesPerStrip, summaryInfo.QuantityOfDevicesPerStrip.ToString());
//14、批次理论芯片数
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStartQuantity, summaryInfo.NumberOfStartQuantity.ToString());
//15、批次已检测芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesInspected, summaryInfo.NumberOfDevicesInspected.ToString());
//16、每小时检测芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DevicesPerHour, string.Format("{0:0.##}", summaryInfo.DevicesPerHour));
rowIndex++;
//17、合格芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesPassed, summaryInfo.NumberOfDevicesPassed.ToString());
//18、不合格的芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesRejected, summaryInfo.NumberOfDevicesRejected.ToString());
//19、误检芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesFalseCalled, summaryInfo.NumberOfDevicesFalseCalled.ToString());
//新增 OK复看为NG数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "OK复看NG数", summaryInfo.NumberOfReviewNG.ToString());
//20、跳过的芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfNoDies, summaryInfo.NumberOfNoDies.ToString());
//21、合格率 存在二维码时根据二维码总数
if (ReportModel == 1)
{
//二维码总数 只在武汉二维码报表模式生成
CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}", "二维码总数"), summaryInfo.CodeNumber.ToString());
double temp = summaryInfo.NumberOfDevicesPassed / (double)summaryInfo.CodeNumber;
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", temp.ToString()));
}
else
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", summaryInfo.YieldByDevice));
}
//22、误检率
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.FalseCallDevicePercent, string.Format("{0:0.##}", summaryInfo.FalseCallDevicePercent));
//新增 当存在 K2N时显示算上复看OK到KG的误检率
if (summaryInfo.NumberOfReviewNG != 0)
{
// CreateOneRowTwoColumnCells(sheet, ref rowIndex, "误检率（%包含复看不合格）", string.Format("{0:0.##}", summaryInfo.DevicePercentOfK2N));
}
rowIndex++;
//23、错误种类出现数量
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType, ExcelText.Count);
foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport)
{
if (defectTypeInfoReport.Count > 0)
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", defectTypeInfoReport.Index, defectTypeInfoReport.DefectType),
defectTypeInfoReport.Count.ToString());
}
}
rowIndex++;
//24、错误种类根据错误优先级统计数量
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType + "(根据错误优先级表)", ExcelText.Count);
if (defectStatistics.Flag == false)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告，存在未规定的错误优先级", "");
}
else
{
foreach (KeyValuePair <int, int> ds in defectStatistics.CountDefectResult)
{
if (defectStatistics.CountN2KDefectResult.ContainsKey(ds.Key))
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType),
string.Format("{0}-{1}={2}", ds.Value, defectStatistics.CountN2KDefectResult[ds.Key], ds.Value - defectStatistics.CountN2KDefectResult[ds.Key]));
}
else
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType),
ds.Value.ToString());
}
}
//25、是否存在优先级相同并且一同输出的
if (defectStatistics.List_RepeatPriority.Count != 0)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告 存在优先级相同情况", "");
for (int i = 0; i < defectStatistics.List_RepeatPriority.Count; i++)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectStatistics.List_RepeatPriority[i].ToString(), "");
}
}
}
sheet.SetColumnWidth(0, 80 * 256);
sheet.SetColumnWidth(0, 30 * 256);
#endregion
workbook.CreateSheet(ExcelText.SheetName_MapOperator);
workbook.CreateSheet(ExcelText.SheetName_UDD);
int T = 0;
#region 创建错误图片Sheet
sheet = workbook.CreateSheet("错误图片");
int sheetIndex = workbook.GetSheetIndex(sheet);
sheet.SetColumnWidth(1, 30 * 256);
sheet.SetColumnWidth(2, 30 * 256);
int ImageSheetNum = 0;
T = T + rowIndex;
rowIndex = 0;
foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix)
{
if (rowIndex >= 65000)
{
ImageSheetNum++;
sheet = workbook.CreateSheet("错误图片续_" + ImageSheetNum);
sheetIndex = workbook.GetSheetIndex(sheet);
rowIndex = 0;
}
HSSFPatriarch sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFCell cell = null;
int defectCountInFrame = 0;
//int sheetContinuedIndexEachStrip = 0;
int ImageCol = 0;
foreach (InspectionDataReport dataReport in inspectionDataReportMatrix)
{
defectCountInFrame++;
int TempImage_row = 0;
ImageCol = 5;
int startRow = 0;
if (dataReport == null)
{
continue;
}
if (dataReport.List_DefectData.Count == 0)
{
if (dataReport.InspectionResult != InspectionResult.K2N)
{
continue;
}
}
startRow = rowIndex;
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0);
cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1));
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
cell.SetCellValue(string.Format("第{0}条", dataReport.FrameIndex));
if (dataReport.Code2D != "null")
{
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(2);
cell.SetCellValue(string.Format("二维码：{0}", dataReport.Code2D));
}
// 创建图片 所有缺陷在一张图中 如果存在拍多次的情况 进行横排 第一张图 col为5
System.Drawing.Image[] images = new System.Drawing.Image[dataReport.List_GeneralImageTempPath.Count];
for (int i = 0; i < images.Count(); i++)
{
//创建图片单元格
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(ImageCol);
if (File.Exists(dataReport.List_GeneralImageTempPath[i]))
{
byte[] bytes = File.ReadAllBytes(dataReport.List_GeneralImageTempPath[i]);
int index = workbook.AddPicture(bytes, PictureType.JPEG);
System.Drawing.Image image;
using (MemoryStream ms = new MemoryStream(bytes))
{
image = System.Drawing.Image.FromStream(ms);
}
double imageWidth = image.Width;
double imageHeight = image.Height;
image.Dispose();
double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
int imageInCellColumns = (int)(imageWidth / cellWidth);
int imageInCellRows = (int)(imageHeight / cellHeight);
double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, ImageCol, rowIndex, imageInCellColumns + ImageCol, rowIndex + imageInCellRows);
sheetPatriarch.CreatePicture(anchor, index);
//计算图片高占多少个单元格
TempImage_row = (int)Math.Ceiling(imageHeight / cellHeight) + 1;
NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex + TempImage_row, 1);
dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString());
}
else
{
cell.SetCellValue("图片不存在");
}
ImageCol = ImageCol + 10;
}
rowIndex = rowIndex + 1;
//记录错误信息
foreach (var defectReort in dataReport.List_DefectData)
{
rowIndex = rowIndex + 1;
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
if (defectReort.DefectTypeIndex <= 0 || defectReort.DefectTypeIndex > list_DefectTypeInfoReport.Count)
{
cell.SetCellValue(string.Format("{0}:{1}", defectReort.DefectTypeIndex, defectReort.ErrorDetail));
}
else
{
cell.SetCellValue(string.Format("{0}:{1}", list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType, defectReort.ErrorDetail));
}
}
rowIndex = rowIndex + 1;
string[] show_defecttype = new string[dataReport.List_DefectData.Count];
for (int i = 0; i < dataReport.List_DefectData.Count; i++)
{
show_defecttype[i] = dataReport.List_DefectData[i].DefectTypeIndex.ToString();
}
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
cell.SetCellValue(string.Format("详细错误码：{0}", string.Join(";", show_defecttype)));
//判断 图片所占行数多 还是写入的错误信息行数多
if (rowIndex - startRow <= TempImage_row)
{
rowIndex = startRow + TempImage_row + 2;
}
#region 每个缺陷对应一张图 已屏蔽
//foreach (var defectReort in dataReport.List_DefectData)
//{
// if (rowIndex>= 32757)
// {
// sheetContinuedIndexEachStrip++;
// sheet = workbook.CreateSheet(inspectionDataReportMatrix.FrameName + " " + ExcelText.Continued + sheetContinuedIndexEachStrip.ToString());
// sheetIndex = workbook.GetSheetIndex(sheet);
// sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
// rowIndex = 0;
// }
// defectCountInFrame++;
// sheet.CreateRow(rowIndex);
// sheet.CreateRow(rowIndex + 1);
// HSSFCell cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0);
// cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1));
// cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
// cell.SetCellValue(list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType);
// cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(1);
// cell.SetCellValue(defectReort.ErrorDetail);
// rowIndex += 2;
// sheet.CreateRow(rowIndex);
// cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
// if (File.Exists(defectReort.ImageTempPath))
// {
// byte[] bytes = File.ReadAllBytes(defectReort.ImageTempPath);
// int index = workbook.AddPicture(bytes, PictureType.JPEG);
// System.Drawing.Image image;
// using (MemoryStream ms = new MemoryStream(bytes))
// {
// image = System.Drawing.Image.FromStream(ms);
// }
// double imageWidth = image.Width;
// double imageHeight = image.Height;
// image.Dispose();
// double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// int imageInCellColumns = (int)(imageWidth / cellWidth);
// int imageInCellRows = (int)(imageHeight / cellHeight);
// double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
//
// //HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// //commentAnchor.AnchorType = AnchorType.MoveDontResize;
// //HSSFComment comment = (HSSFComment)sheetPatriarch.CreateCellComment(commentAnchor);
// //comment.SetBackgroundImage(index);
// //cell.CellComment = (comment);
// HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 1, rowIndex, imageInCellColumns + 1, rowIndex + imageInCellRows);
// sheetPatriarch.CreatePicture(anchor, index);
// if (!hasRecordLink)
// {
// NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex, 1);
// dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString());
// hasRecordLink = true;
// }
// rowIndex += (int)Math.Ceiling(imageHeight / cellHeight) + 1;
// }
// else
// {
// cell.SetCellValue("图片不存在");
// }
//}
#endregion
}
if (defectCountInFrame == 0)
{
workbook.RemoveSheetAt(sheetIndex);
}
}
#endregion
#region MapOperator
sheet = workbook.GetSheet(ExcelText.SheetName_MapOperator);
if (ReportModel != 1)
{
sheet.DefaultColumnWidth = 1;
}
//sheet = workbook.GetSheetAt(1);
rowIndex = 0;
//1、产品编号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode);
//2、批次号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot);
//3、设备号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine);
//4、操作员
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator);
rowIndex++;
//5、检测结果样例
Dictionary <InspectionResult, ICellStyle> dict_result_style = new Dictionary <InspectionResult, ICellStyle>();
ICellStyle exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Green;
dict_result_style.Add(InspectionResult.OK, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.OK), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Red;
dict_result_style.Add(InspectionResult.NG, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.NG), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Yellow;
dict_result_style.Add(InspectionResult.N2K, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.N2K), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Orange;
dict_result_style.Add(InspectionResult.K2N, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.K2N), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.SkyBlue;
dict_result_style.Add(InspectionResult.SKIP, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.SKIP), exampleStyle);
rowIndex++;
//6、分盘绘制图谱
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
ICellStyle centerAlignmentStyle = workbook.CreateCellStyle();
centerAlignmentStyle.Alignment = HorizontalAlignment.Center;
int sheetContinuedIndex = 0;
foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix)
{
if (inspectionDataReportMatrix.ColumnCount < 254)
{
//创建单元格
for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++)
{
IRow createRow = sheet.CreateRow(rowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++)
{
createRow.CreateCell(j);
}
}
//赋值条号
sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
{
ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2);
cell.SetCellValue((i + 1).ToString());
cell.CellStyle = centerAlignmentStyle;
}
//创建二维码列表
if (ReportModel == 1)
{
code2drowIndex = rowIndex + inspectionDataReportMatrix.RowCount + 2;
//创建单元格
for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++)
{
IRow createRow = sheet.CreateRow(code2drowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++)
{
createRow.CreateCell(j);
}
}
//赋值二维码
sheet.GetRow(code2drowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", "二维码", inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
sheet.GetRow(code2drowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
//for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
//{
// ICell cell = sheet.GetRow(code2drowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2);
// //cell.SetCellValue((i + 1).ToString());
// cell.CellStyle = centerAlignmentStyle;
//}
foreach (InspectionDataReport data1 in inspectionDataReportMatrix)
{
if (data1 == null)
{
continue;
}
ICell cell;
if (inspectionDataReportMatrix.ColumnCount < 254)
{
cell = sheet.GetRow(code2drowIndex + data1.RowIndex).GetCell(data1.ColumnIndex + 2);
}
else
{
cell = sheet.GetRow(code2drowIndex + data1.ColumnIndex).GetCell(data1.RowIndex + 2);
}
cell.SetCellValue(data1.Code2D);
}
}
}
else
{
if (rowIndex + inspectionDataReportMatrix.ColumnCount + 1 >= 32757)
{
sheetContinuedIndex++;
sheet = workbook.CreateSheet(ExcelText.SheetName_MapOperator + " " + ExcelText.Continued + sheetContinuedIndex.ToString());
rowIndex = 0;
}
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount + 1; i++)
{
IRow createRow = sheet.CreateRow(rowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.RowCount + 2; j++)
{
createRow.CreateCell(j);
}
}
sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
{
sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.ColumnCount).GetCell(i + 2);
cell.SetCellValue((i + 1).ToString());
cell.CellStyle = centerAlignmentStyle;
}
}
foreach (InspectionDataReport data in inspectionDataReportMatrix)
{
if (data == null)
{
continue;
}
ICell cell;
if (inspectionDataReportMatrix.ColumnCount < 254)
{
cell = sheet.GetRow(rowIndex + data.RowIndex).GetCell(data.ColumnIndex + 2);
}
else
{
cell = sheet.GetRow(rowIndex + data.ColumnIndex).GetCell(data.RowIndex + 2);
}
cell.CellStyle = dict_result_style[data.InspectionResult];
if (data.InspectionResult == InspectionResult.NG)
{
//string[] defectTypes = new string[data.List_DefectData.Count];
//for (int i = 0; i < data.List_DefectData.Count; i++)
//{
// defectTypes[i] = data.List_DefectData[i].DefectTypeIndex.ToString();
//}
string[] noRepeatDefectTyoes = new string[data.Priority_DetectType.Count];
for (int i = 0; i < data.Priority_DetectType.Count; i++)
{
noRepeatDefectTyoes[i] = data.Priority_DetectType[i].ToString();
}
//string[] noRepeatDefectTyoes = data.List_DefectData.Select(d => d.DefectTypeIndex.ToString()).Distinct().ToArray();
cell.SetCellValue(string.Join(";", noRepeatDefectTyoes));
}
if (data.InspectionResult == InspectionResult.NG || data.InspectionResult == InspectionResult.N2K || data.InspectionResult == InspectionResult.K2N)
{
if (data.ExcelDefectImageLink == null)
{
continue;
}
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = data.ExcelDefectImageLink;
cell.Hyperlink = link;
}
#region 对NG图 以及 N2K图添加批注 鼠标悬停可以显示图片 已屏蔽
//if (data.InspectionResult == InspectionResults.NG || data.InspectionResult == InspectionResults.N2K)
//{
// //int defectCount = data.List_DefectData.Count;
// //System.Drawing.Image[] images = new System.Drawing.Image[defectCount];
// System.Drawing.Image[] images = new System.Drawing.Image[data.List_GeneralImageTempPath.Count];
// double imageMaxWidth = 0;
// double imageTotalHeight = 0;
// //for (int i = 0; i < defectCount; i++)
// //{
// // if (!File.Exists(data.List_DefectData[i].ImageTempPath)) break;
// // byte[] bytesOfImage = File.ReadAllBytes(data.List_DefectData[i].ImageTempPath);
// // using (MemoryStream ms = new MemoryStream(bytesOfImage))
// // {
// // images[i] = System.Drawing.Image.FromStream(ms);
// // }
// // if (images[i].Width > imageMaxWidth)
// // {
// // imageMaxWidth = images[i].Width;
// // }
// // imageTotalHeight += images[i].Height;
// //}
// for (int i = 0; i < images.Count(); i++)
// {
// if (!File.Exists(data.List_GeneralImageTempPath[i])) break;
// byte[] bytesOfImage = File.ReadAllBytes(data.List_GeneralImageTempPath[i]);
// using (MemoryStream ms = new MemoryStream(bytesOfImage))
// {
// images[i] = System.Drawing.Image.FromStream(ms);
// }
// if (images[i].Width > imageMaxWidth)
// {
// imageMaxWidth = images[i].Width;
// }
// imageTotalHeight += images[i].Height;
// }
// if (imageMaxWidth == 0 || imageTotalHeight == 0) continue;
// Bitmap concatImage = new Bitmap((int)imageMaxWidth, (int)imageTotalHeight);
// concatImage.SetResolution(300, 300);
// Graphics g = Graphics.FromImage(concatImage);
// float drawStartHeight = 0;
// for (int i = 0; i < images.Count(); i++)
// {
// g.DrawImage(images[i], 0, drawStartHeight);
// drawStartHeight += images[i].Height;
// images[i].Dispose();
// }
// byte[] bytesOfConcatImage;
// using (MemoryStream ms = new MemoryStream())
// {
// concatImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
// bytesOfConcatImage = ms.ToArray();
// }
// int index = workbook.AddPicture(bytesOfConcatImage, PictureType.JPEG);
// double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip)
// //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI
// //要计算Excel的行高，就先把它行转换到英寸，再乘小DPI就可以得到像素
// //像素 = (Excel的行高度 / 72) * DPI
// double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// int imageInCellColumns = (int)(imageMaxWidth / cellWidth);
// int imageInCellRows = (int)(imageTotalHeight / cellHeight);
// double offsetX = (imageMaxWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// double offsetY = (imageTotalHeight - cellHeight * imageInCellRows) / cellHeight * 256;
// IClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// commentAnchor.AnchorType = AnchorType.MoveDontResize;
// HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor);
// comment.SetBackgroundImage(index);
// cell.CellComment = comment;
// HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
// link.Address = data.ExcelDefectImageLink;
// cell.Hyperlink = link;
// //NPOI.SS.Util.CellReference cr = new NPOI.SS.Util.CellReference("A1");
// //ICellStyle hlink_style = hssfworkbook.CreateCellStyle();
// //IFont hlink_font = hssfworkbook.CreateFont();
// //hlink_font.Underline = FontUnderlineType.Single;
// //hlink_font.Color = HSSFColor.Blue.Index;
// //hlink_style.SetFont(hlink_font);
// //cell.CellStyle = (hlink_style);
// //HSSFCell picIndexCell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(picColumnIndex);
// //picIndexCell.SetCellValue(string.Format("{0}-{1}", data.Row + 1, data.Column + 1));
// //picIndexCell.CellStyle = centerAlignmentStyle;
// //if (!data.PicturePath.Equals(string.Empty))
// //{
// // if (File.Exists(data.PicturePath))
// // {
// // byte[] bytes = File.ReadAllBytes(data.PicturePath);
// // int index = workbook.AddPicture(bytes, PictureType.JPEG);
// // System.Drawing.Image image;
// // using (MemoryStream ms = new MemoryStream(bytes))
// // {
// // image = System.Drawing.Image.FromStream(ms);
// // }
// // double imageWidth = image.Width;
// // double imageHeight = image.Height;
// // image.Dispose();
// // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// // //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip)
// // //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI
// // //要计算Excel的行高，就先把它行转换到英寸，再乘小DPI就可以得到像素
// // //像素 = (Excel的行高度 / 72) * DPI
// // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// // int imageInCellColumns = (int)(imageWidth / cellWidth);
// // int imageInCellRows = (int)(imageHeight / cellHeight);
// // double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// // double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
// // HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// // commentAnchor.AnchorType = AnchorType.MoveDontResize;
// // HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor);
// // comment.SetBackgroundImage(index);
// // cell.CellComment = (comment);
// // //HSSFClientAnchor anchor = new HSSFClientAnchor(100, 0, 923, 0, picColumnIndex, rowIndex + 1, picColumnIndex, rowIndex + dieDataSQLMatrix.RowCount);
// // //patriarch.CreatePicture(anchor, index);
// // }
// //}
// //else
// //{
// // cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(picColumnIndex);
// // cell.SetCellValue("图片不存在");
// // cell.CellStyle = centerAlignmentStyle;
// //}
// //picColumnIndex++;
//}
#endregion
}
if (inspectionDataReportMatrix.ColumnCount < 254)
{
if (ReportModel == 1)
{
rowIndex = code2drowIndex;
}
rowIndex += inspectionDataReportMatrix.RowCount + 2;
}
else
{
rowIndex += inspectionDataReportMatrix.ColumnCount + 2;
}
}
sheet.SetColumnWidth(0, 20 * 256);
#endregion
#region UDD
sheet = workbook.GetSheet(ExcelText.SheetName_UDD);
//sheet = workbook.GetSheetAt(2);
rowIndex = 0;
foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectTypeInfoReport.Index.ToString(), defectTypeInfoReport.DefectType);
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
return(ms);
}
}
