public ExcalHelper(String sheetName, String[] rowTitle, int[] rowWidth, int dataRowNum)
{
//创建标题行
workbook = new HSSFWorkbook();
sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
this.dataRowNum = dataRowNum;
HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < rowTitle.Length; i++)
{
//设置列宽
sheet.SetColumnWidth(i, rowWidth[i] * 255);
rowtitle.CreateCell(i).SetCellValue(rowTitle[i]);
}
}
/// <summary>
/// 插入Excel行
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
/// <param name="row"></param>
private static void MyInsertRow(HSSFSheet sheet, int rowIndex, int count, HSSFRow row)
{
#region 批量移动行
sheet.ShiftRows(
rowIndex, //--开始行
sheet
.LastRowNum, //--结束行
count, //--移动大小(行数)--往下移动
true, //是否复制行高
false, //是否重置行高
true //是否移动批注
);
#endregion
#region 对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
for (int i = rowIndex; i < rowIndex + count - 1; i++)
{
HSSFRow targetRow;
HSSFCell sourceCell;
HSSFCell targetCell;
targetRow = sheet.CreateRow(i + 1);
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
sourceCell = row.GetCell(m);
if (sourceCell == null)
continue;
targetCell = targetRow.CreateCell(m);
targetCell.Encoding = sourceCell.Encoding;
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
}
//CopyRow(sourceRow, targetRow);
//Util.CopyRow(sheet, sourceRow, targetRow);
}
HSSFRow firstTargetRow = sheet.GetRow(rowIndex);
HSSFCell firstSourceCell;
HSSFCell firstTargetCell = null;
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
firstSourceCell = row.GetCell(m);
if (firstSourceCell == null)
continue;
firstTargetCell = firstTargetRow.CreateCell(m);
firstTargetCell.Encoding = firstSourceCell.Encoding;
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
sheet.AddMergedRegion(new Region(firstTargetRow.RowNum, 3, firstTargetRow.RowNum, 4));
#endregion
}
private void SetTeacherSalary(HSSFSheet sheet, string teacherName, string termTag, int teacherType, int status)
{
Dictionary<string, int> inSalaryItemIndex = new Dictionary<string, int>();
Dictionary<string, int> outSalaryItemIndex = new Dictionary<string, int>();
int cIndex = 0;
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("姓名");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("类型");
if (teacherType != 1)
{
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("课程");
}
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("薪酬预算");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("学期");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("状态");
List<SalaryItem> salaryItems = GetSalaryItem(teacherType);
int count = salaryItems.Count;
for (var i = 0; i < count; i++)
{
sheet.CreateRow(1).CreateCell(cIndex + i).SetCellValue(salaryItems[i].salaryItemName);
inSalaryItemIndex.Add(salaryItems[i].salaryItemId.ToString(), cIndex + i);
}
sheet.CreateRow(0).CreateCell(0).SetCellValue("基本信息");
sheet.CreateRow(0).CreateCell(cIndex).SetCellValue("薪酬预算");
List<TeacherSalary> teacherSalaries = QueryTeacherSalaries(teacherName, termTag, teacherType, status);
TeacherSalary teacherSalary;
double totalTeachCost = 0;
for (var i = 0; i < teacherSalaries.Count; i++)
{
cIndex = 0;
teacherSalary = teacherSalaries[i];
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(teacherSalary.teacher.teacherName);
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(CommonUtility.ConvertTeacherType2String(teacherSalary.teacherType));
if (teacherType != 1)
{
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(teacherSalary.course == null ? "" : teacherSalary.course.courseName);
}
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(teacherSalary.totalTeachCost);
totalTeachCost += teacherSalary.totalTeachCost;
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(teacherSalary.termTag);
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(teacherSalary.isConfirm ? "已确认" : "未确认");
List<SalaryItemElement> inItemElements = teacherSalary.GetSalaryInItemElements();
if (inItemElements != null)
{
foreach (SalaryItemElement itemElement in inItemElements)
{
sheet.CreateRow(i + 2).CreateCell(inSalaryItemIndex[itemElement.salaryItemId.ToString()]).SetCellValue(itemElement.itemCost);
}
}
}
cIndex = 2;
sheet.CreateRow((cIndex++) + teacherSalaries.Count).CreateCell(0).SetCellValue("-----");
sheet.CreateRow((cIndex++) + teacherSalaries.Count).CreateCell(0).SetCellValue("合计薪酬金额：" + totalTeachCost);
}
/// <summary>
/// HSSFRow Copy Command
///
/// Description: Inserts a existing row into a new row, will automatically push down
/// any existing rows. Copy is done cell by cell and supports, and the
/// command tries to copy all properties available (style, merged cells, values, etc...)
/// </summary>
/// <param name="workbook">Workbook containing the worksheet that will be changed</param>
/// <param name="worksheet">WorkSheet containing rows to be copied</param>
/// <param name="sourceRowNum">Source Row Number</param>
/// <param name="destinationRowNum">Destination Row Number</param>
private HSSFRow CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
{
// Get the source / new row
var newRow = (HSSFRow)worksheet.GetRow(destinationRowNum);
var sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
}
else
{
newRow = (HSSFRow)worksheet.CreateRow(destinationRowNum);
}
// Loop through source columns to add to new row
for (var i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
var oldCell = (HSSFCell)sourceRow.GetCell(i);
var newCell = (HSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null) continue;
// Copy style from old cell and apply to new cell
var newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
newCell.CellStyle = newCellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.BLANK:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.BOOLEAN:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.ERROR:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.FORMULA:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.NUMERIC:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.STRING:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (var i = 0; i < worksheet.NumMergedRegions; i++)
{
var cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow != sourceRow.RowNum) continue;
var newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.FirstRow -
cellRangeAddress.LastRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
return newRow;
}
private void InsertRows(HSSFSheet sheet1, int fromRowIndex, int rowCount)
{
sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false, true);
for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
{
IRow rowSource = sheet1.GetRow(rowIndex + rowCount);
IRow rowInsert = sheet1.CreateRow(rowIndex);
rowInsert.Height = rowSource.Height;
for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
{
ICell cellSource = rowSource.GetCell(colIndex);
ICell cellInsert = rowInsert.CreateCell(colIndex);
if (cellSource != null)
{
cellInsert.CellStyle = cellSource.CellStyle;
}
}
}
}
public ActionResult ExportReportExcel(DateTime selected_date)
{
String company_name = "DANGOTE CEMENT";
List <view_message_dto> _sms_list = TempData["sms_list"] as List <view_message_dto>;
// Opening the Excel template...
FileStream fs =
new FileStream(Server.MapPath(@"\Content\template\reporting_sms_List_Selected.xls"), FileMode.Open, FileAccess.Read);
MemoryStream ms = new MemoryStream();
// Getting the complete workbook...
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
try
{
// Getting the worksheet by its name...
NPOI.HSSF.UserModel.HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("SmsList");
//RO's Name
NPOI.HSSF.UserModel.HSSFRow dataRow = (HSSFRow)sheet.GetRow(3);
ICell cell_default = dataRow.Cells[0];
ICellStyle boldStyle = cell_default.CellStyle;
dataRow = (HSSFRow)sheet.GetRow(0);
dataRow.Cells[0].SetCellValue(company_name);
int Row = 3;
foreach (view_message_dto item in _sms_list)
{
dataRow = (HSSFRow)sheet.CreateRow(Row); //
dataRow.CreateCell(0).SetCellValue(Row - 2);
dataRow.CreateCell(1).SetCellValue(item.atc_no);
dataRow.CreateCell(2).SetCellValue(item.delivery_no);
dataRow.CreateCell(3).SetCellValue(item.customer_no);
dataRow.CreateCell(4).SetCellValue(item.phone_no);
dataRow.CreateCell(5).SetCellValue(item.waybill_no);
dataRow.CreateCell(6).SetCellValue(item.customer_name);
dataRow.CreateCell(7).SetCellValue(item.ip_address);
dataRow.CreateCell(8).SetCellValue(item.code_a);
dataRow.CreateCell(9).SetCellValue(item.code_b);
dataRow.CreateCell(10).SetCellValue(item.sms_status);
dataRow.CreateCell(11).SetCellValue(item.response);
dataRow.CreateCell(12).SetCellValue(item.create_datetime.ToString());
dataRow.CreateCell(13).SetCellValue(item.response_datetime.ToString());
if (Row % 2 == 0)
{
for (int i = 0; i < 5; i++)
{
ICell cell = dataRow.Cells[i];
cell.CellStyle = boldStyle;
}
}
Row++;
}
sheet.ForceFormulaRecalculation = true;
TempData["Message"] = "Excel report created successfully!";
}
catch (Exception ex)
{
TempData["Message"] = "Oops! Something went wrong." + "<br/>" + ex.Message.ToString();
}
String FileName = "sms_list_report_" + selected_date.ToString("-yyyy-MM-dd") + ".xls";
// Writing the workbook content to the FileStream...
templateWorkbook.Write(ms);
return(File(ms.ToArray(), "application/vnd.ms-excel", FileName));
}
public static void populateStrategyDetailData(HSSFWorkbook wb, HSSFSheet sheet, IEnumerable<ArchitecturalStrategy> data)
{
#region workbookStyles
//add Styles to workbook
HSSFCellStyle styleMiddle = wb.CreateCellStyle();
styleMiddle.Alignment = CellHorizontalAlignment.CENTER;
HSSFCellStyle styleLeftWrap = wb.CreateCellStyle();
styleLeftWrap.Alignment = CellHorizontalAlignment.LEFT;
styleMiddle.VerticalAlignment = CellVerticalAlignment.CENTER;
styleLeftWrap.WrapText = true; //wrap the text in the cell
//----------------------------------------------------------
//font style1: italic, blue color, fontsize=20
HSSFFont font1 = wb.CreateFont();
font1.Color = HSSFColor.BLUE.index;
font1.IsItalic = true;
font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font1.Underline = (byte)HSSFBorderFormatting.BORDER_THIN;
// font1.Underline = (byte)FontUnderlineType.DOUBLE;
// font1.FontHeightInPoints = 20;
//bind font with styleItalicBold
HSSFCellStyle italicBold = wb.CreateCellStyle();
italicBold.SetFont(font1);
//----------------------------------------------------------
//bind font with styleItalicBold
HSSFCellStyle underline = wb.CreateCellStyle();
underline.BorderBottom = CellBorderType.THIN;
underline.BottomBorderColor = HSSFColor.BLUE_GREY.index;
HSSFCellStyle topline = wb.CreateCellStyle();
topline.BorderTop = CellBorderType.THIN;
topline.TopBorderColor = HSSFColor.BLUE_GREY.index;
#endregion
//set headerRow and 1st column
const int maxRows = 65536; //npoi uses excel 2003
int hRowNum = 4; //row starts at 0.
int startCol = 0;
int errorRow = 2; //note errors
int errorCol = 6; //note errors
HSSFRow headerRow = sheet.GetRow(hRowNum);
int colIndex = startCol;
#region Headers
//date
sheet.GetRow(0).GetCell(1).SetCellValue(DateTime.Now);
//Title
sheet.GetRow(1).GetCell(1).SetCellValue(data.FirstOrDefault().Project.Name);
// handling headers.
headerRow.GetCell(colIndex).SetCellValue("Strategy");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Name");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Description");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Scenarios Affected");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Current Response");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Expected Response");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Current Utility");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Expected Utility");
colIndex++;
#endregion //headers
#region populateData
// foreach (DataColumn column in propertyInfos)
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = hRowNum + 1;
HSSFRow dataRow = sheet.CreateRow(rowIndex);
Boolean newStrategyRow = true;
var i = 0; //index for loops
try
{
foreach (var item in data)
{
dataRow = sheet.CreateRow(rowIndex);
if (rowIndex < maxRows - 1)
{
//write each field
newStrategyRow = true;
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue(item.ID);
dataRow.GetCell(colIndex).CellStyle = topline;
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Name.ToString());
dataRow.GetCell(colIndex).CellStyle = topline;
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Description.ToString());
dataRow.GetCell(colIndex).CellStyle = topline;
colIndex++;
var subcol = colIndex;
foreach (var s in item.ExpectedUtilities)
{
dataRow = sheet.CreateRow(rowIndex);
colIndex = subcol;
var currentUtil = s.Scenario.Utilities.Where(y => y.QualityAttributeResponseTypeID == "C").FirstOrDefault();
//ID of scenario affected
dataRow.CreateCell(colIndex).SetCellValue(s.Scenario.ID);
colIndex++;
//current description
dataRow.CreateCell(colIndex).SetCellValue(currentUtil.Description.ToString());
dataRow.GetCell(colIndex).CellStyle = styleLeftWrap;
colIndex++;
//expected description
dataRow.CreateCell(colIndex).SetCellValue(s.ExpectedUtilityDescription.ToString());
colIndex++;
//current utility
dataRow.CreateCell(colIndex).SetCellValue(currentUtil.Utility1.Value);
colIndex++;
//expected utility
dataRow.CreateCell(colIndex).SetCellValue(s.ExpectedUtility1.Value);
#region Add formats to top of a strategy
if (newStrategyRow) //set formats for new strat row
{ i = subcol;
while (i <= colIndex)
{
dataRow.GetCell(i).CellStyle = topline;
i++;
}
}
#endregion
rowIndex++;
newStrategyRow = false;
if (rowIndex >= maxRows - 1)
{throw new InvalidDataException();}
}//end expected utilities
#region Add formats to bottom of strategy
dataRow = sheet.CreateRow(rowIndex);
i = startCol;
while (i <= colIndex)//add formats to subsection w/utility info
{
dataRow.CreateCell(i).CellStyle = underline;
i++;
}
#endregion
//end of strategy
rowIndex++;
}//end if check max rows
#endregion
#region errors
else
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue("Dataset exceeds maximum number of rows");
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Data Exceeds max records");
}
}//end data loop
}// end try
catch
{
colIndex = startCol;
if (sheet.GetRow(errorRow).LastCellNum >= errorCol) //error cell exists
{
sheet.GetRow(errorRow).GetCell(errorCol).SetCellValue("Error Occured");
}
else
{//create cell for error message
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Error Occured");
}
}
#endregion
}
/// <summary>
/// Get a row from the spreadsheet, and Create it if it doesn't exist.
/// </summary>
/// <param name="rowCounter">The 0 based row number</param>
/// <param name="sheet">The sheet that the row is part of.</param>
/// <returns>The row indicated by the rowCounter</returns>
public static NPOI.SS.UserModel.IRow GetRow(int rowCounter, HSSFSheet sheet)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return row;
}
private void SetProjectReimSheet(HSSFSheet projectSheet, List<ReimItem> reimItemList)
{
int cIndex = 0;
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("项目名称");
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("报销项名称");
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("报销金额");
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("报销时间");
if (reimItemList != null) {
for (int rIndex = 0; rIndex < reimItemList.Count; rIndex++)
{
cIndex = 0;
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(reimItemList[rIndex].project.name);
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(reimItemList[rIndex].reim.name);
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(reimItemList[rIndex].value);
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(reimItemList[rIndex].createdTime.ToString());
}
}
}
private void SetProjectSummarySheet(HSSFSheet projectSummarySheet, List<ReimItem> projectReimSummaryList)
{
int cIndex = 0;
projectSummarySheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("项目名称");
projectSummarySheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("报销金额");
if (projectReimSummaryList != null)
{
for (int rIndex = 0; rIndex < projectReimSummaryList.Count; rIndex++)
{
cIndex = 0;
projectSummarySheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(projectReimSummaryList[rIndex].project.name);
projectSummarySheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(projectReimSummaryList[rIndex].value);
}
}
}
/// <summary>
/// 往单元格写数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="columnIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static HSSFCell WriteCell(HSSFSheet sheet, int columnIndex, int rowIndex, object obj)
{
HSSFRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
return WriteCell(row, obj, columnIndex);
}
/// <summary>
/// 写入行
/// </summary>
public static HSSFRow WriteRow(HSSFSheet sheet, object[] dr, int rowIndex, int columnIndex)
{
HSSFRow row = sheet.CreateRow(rowIndex);
int i = 0;
while (i < dr.Length)
{
WriteCell(row, dr[i], i);
i++;
columnIndex++;
}
return row;
}
private void SetProjectSheet(HSSFSheet projectSheet, List<Project> projectList)
{
int cIndex = 0;
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("项目名称");
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("项目负责人");
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("项目类别");
projectSheet.CreateRow(0).CreateCell(cIndex++).SetCellValue("登记时间");
for (int rIndex = 0; rIndex < projectList.Count; rIndex++)
{
cIndex = 0;
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(projectList[rIndex].name);
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(projectList[rIndex].userName);
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(projectList[rIndex].category.name);
projectSheet.CreateRow(rIndex + 1).CreateCell(cIndex++).SetCellValue(projectList[rIndex].createdTime.ToString());
}
}
// IEnumerable<TestData> data = db.TestDatas;
public static void populateResponseGoalData(HSSFWorkbook wb, HSSFSheet sheet, IEnumerable<Scenario> data)
{
//set headerRow and 1st column
const int maxRows = 65536; //npoi uses excel 2003
int hRowNum = 4; //row starts at 0.
int startCol = 0;
HSSFRow headerRow = sheet.GetRow(hRowNum);
int colIndex = startCol;
int errorRow = 2; //note errors
int errorCol = 6; //note errors
#region headers
//date
sheet.GetRow(0).GetCell(1).SetCellValue(DateTime.Now);
//Title
sheet.GetRow(1).GetCell(1).SetCellValue(data.FirstOrDefault().Project.Name);
// handling headers.
headerRow.GetCell(colIndex).SetCellValue("Priority");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Name");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Votes");
colIndex++;
foreach (var utilityitem in data.FirstOrDefault().Utilities)
{
//write each field
headerRow.GetCell(colIndex).SetCellValue(utilityitem.QualityAttributeResponseType.Type.ToString() + " Response Goal");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue(utilityitem.QualityAttributeResponseType.Type.ToString()+ " Utility");
colIndex++;
// if (utilityitem.Utility1.HasValue)//only top 1/6 have utility value
// {
// headerRow.GetCell(colIndex).SetCellValue(utilityitem.Description.ToString());
// }
// colIndex++;
}// end utility item loop
#endregion
#region populateData
// foreach (DataColumn column in propertyInfos)
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = hRowNum + 1;
try{
foreach (var item in data)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex);
if (rowIndex < maxRows - 1)
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue(item.Priority.ToString());
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Name.ToString());
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Votes.Value);
colIndex++;
foreach (var utilityitem in item.Utilities)
{
//write each field
dataRow.CreateCell(colIndex).SetCellValue(utilityitem.Description.ToString());
colIndex++;
if (utilityitem.Utility1.HasValue)//only top 1/6 have utility value
{
dataRow.CreateCell(colIndex).SetCellValue(utilityitem.Utility1.Value);
}
colIndex++;
}// end utility item loop
rowIndex++;
}//end if check max rows
#endregion
#region populateErrors
else
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue("Dataset exceeds maximum number of rows");
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Data Exceeds max records");
}
}//end data loop
}// end try
catch
{
colIndex = startCol;
if (sheet.GetRow(errorRow).LastCellNum >= errorCol) //error cell exists
{
sheet.GetRow(errorRow).GetCell(errorCol).SetCellValue("Error Occured");
}
else
{//create cell for error message
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Error Occured");
}
}
#endregion
//Assumes template file has table named "ScenarioTable"
// retrieve the named range
//string sRangeName = "scenarioData"; //range name
//int snamedRangeIdx = wb.GetNameIndex(sRangeName); //range index
//HSSFName sRange = wb.GetNameAt(snamedRangeIdx); //get range
//String sheetName = wb.GetSheetName(wb.ActiveSheetIndex); //sheet name
//String reference = sheetName +"!A6:C25"; //new area reference
//sRange.SetRefersToFormula(reference); //set range to new area reference
}
/// <summary>
/// Get a row from the spreadsheet, and Create it if it doesn't exist.
/// </summary>
/// <param name="rowCounter">The 0 based row number</param>
/// <param name="sheet">The sheet that the row is part of.</param>
/// <returns>The row indicated by the rowCounter</returns>
public static HSSFRow GetRow(int rowCounter, HSSFSheet sheet)
{
HSSFRow row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return row;
}
private void SetTeacherSalary(HSSFSheet sheet, string teacherName, string termTag, string salaryMonth, int teacherType, int status)
{
Dictionary<string, int> inSalaryItemIndex = new Dictionary<string, int>();
Dictionary<string, int> outSalaryItemIndex = new Dictionary<string, int>();
int cIndex = 0;
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("姓名");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("类型");
if (teacherType != 1)
{
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("课程");
}
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("不含税薪酬");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("含税薪酬");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("税后总薪酬");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("学期");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("月份");
sheet.CreateRow(1).CreateCell(cIndex++).SetCellValue("状态");
List<SalaryItem> salaryItems = GetSalaryItem(teacherType);
int count = salaryItems.Count;
for (var i = 0; i < count; i++)
{
sheet.CreateRow(1).CreateCell(cIndex + i).SetCellValue(salaryItems[i].salaryItemName + (salaryItems[i].hasTax ? "(含税)" : ""));
inSalaryItemIndex.Add(salaryItems[i].salaryItemId.ToString(), cIndex + i);
}
for (var i = 0; i < count; i++)
{
sheet.CreateRow(1).CreateCell(cIndex + count + i).SetCellValue(salaryItems[i].salaryItemName);
outSalaryItemIndex.Add(salaryItems[i].salaryItemId.ToString(), cIndex + i + count);
}
sheet.CreateRow(0).CreateCell(0).SetCellValue("基本信息");
sheet.CreateRow(0).CreateCell(cIndex).SetCellValue("薪酬收入");
if (count == 0)
{
sheet.CreateRow(0).CreateCell(cIndex + 1).SetCellValue("薪酬扣除");
}
else
{
sheet.CreateRow(0).CreateCell(cIndex + count).SetCellValue("薪酬扣除");
}
List<SalaryEntry> teacherSalaries = QuerySalaryEntries(teacherName, termTag, salaryMonth, teacherType, status);
SalaryEntry salaryEntry;
float totalSalaryWithTax = 0;
float totalSalaryWithoutTax = 0;
float totalSalary = 0;
for (var i = 0; i < teacherSalaries.Count; i++)
{
cIndex = 0;
salaryEntry = teacherSalaries[i];
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntry.teacher.teacherName);
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(CommonUtility.ConvertTeacherType2String(salaryEntry.teacherType));
if (teacherType != 1)
{
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntry.course == null ? "" : salaryEntry.course.courseName);
}
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntry.teacherCostWithoutTax);
totalSalaryWithoutTax += salaryEntry.teacherCostWithoutTax;
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntry.teacherCostWithTax);
totalSalaryWithTax += salaryEntry.teacherCostWithTax;
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntry.teacherTotalCost);
totalSalary += salaryEntry.teacherTotalCost;
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntry.termTag);
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntry.salaryMonth);
string salaryEntryStatus = CommonUtility.ConvertSalaryEntryStatus(salaryEntry.salaryEntryStatus);
sheet.CreateRow(i + 2).CreateCell(cIndex++).SetCellValue(salaryEntryStatus);
List<SalaryItemElement> inItemElements = salaryEntry.GetSalaryInItemElements();
List<SalaryItemElement> outItemElements = salaryEntry.GetSalaryOutItemElements();
if (inItemElements != null)
{
foreach (SalaryItemElement itemElement in inItemElements)
{
if (inSalaryItemIndex.Keys.Contains(itemElement.salaryItemId.ToString())) {
sheet.CreateRow(i + 2).CreateCell(inSalaryItemIndex[itemElement.salaryItemId.ToString()]).SetCellValue(itemElement.itemCost);
}
}
}
if (outItemElements != null)
{
foreach (SalaryItemElement itemElement in outItemElements)
{
sheet.CreateRow(i + 2).CreateCell(outSalaryItemIndex[itemElement.salaryItemId.ToString()]).SetCellValue(itemElement.itemCost);
}
}
}
cIndex = 2;
sheet.CreateRow((cIndex++) + teacherSalaries.Count).CreateCell(0).SetCellValue("-----");
sheet.CreateRow((cIndex++) + teacherSalaries.Count).CreateCell(0).SetCellValue("合计含税薪酬：" + totalSalaryWithTax);
sheet.CreateRow((cIndex++) + teacherSalaries.Count).CreateCell(0).SetCellValue("合计不含税薪酬：" + totalSalaryWithoutTax);
sheet.CreateRow((cIndex++) + teacherSalaries.Count).CreateCell(0).SetCellValue("总发放薪酬：" + totalSalary);
}
private void writeDataInExcle(HSSFSheet hssfSheet, ITable table)
{
int rownum = table.RowCount(null);
int colnum = table.Fields.FieldCount;
//int myindex1 = table.FindField("FID");
int myindex2 = table.FindField("FinalScore");
//IField field1 = null;
IField field2 = null;
//field1 = table.Fields.get_Field(myindex1);
field2 = table.Fields.get_Field(myindex2);
// write header
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(0);
//row.CreateCell(0).SetCellValue(field1.Name.ToString());
row.CreateCell(0).SetCellValue(field2.Name.ToString());
// write data
ICursor cursor = table.Search(null, true);
IRow trow = cursor.NextRow();
int i = 1;
while (trow != null)
{
row = hssfSheet.CreateRow(i);
//row.CreateCell(0).SetCellValue(Convert.ToDouble(trow.get_Value(myindex1)));
row.CreateCell(0).SetCellValue(Convert.ToDouble(trow.get_Value(myindex2)));
trow = cursor.NextRow();
i++;
}
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <returns></returns>
static DataTable ImportDt(HSSFSheet sheet, int HeaderRowIndex, bool needHeader)
{
DataTable table = new DataTable();
HSSFRow headerRow;
int cellCount;
try
{
if (HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
HSSFRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as HSSFRow;
}
else
{
row = sheet.GetRow(i) as HSSFRow;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.STRING:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.FORMULA:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.STRING:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
return table;
}
/// <summary>
/// 创建excel表头
/// </summary>
/// <param name="dgv"></param>
/// <param name="excelSheet"></param>
protected static void CreateHeader(HSSFSheet excelSheet)
{
int cellIndex = 0;
//循环导出列
foreach (System.Collections.DictionaryEntry de in ListColumnsName)
{
HSSFRow newRow = excelSheet.CreateRow(0);
HSSFCell newCell = newRow.CreateCell(cellIndex);
newCell.SetCellValue(de.Value.ToString());
cellIndex++;
}
}
public static void populateStrategyBenefitDetails(HSSFWorkbook wb, HSSFSheet sheet, IEnumerable<Benefit> data)
{
//set headerRow and 1st column
const int maxRows = 65535; //npoi uses excel 2003
int hRowNum = 4; //row starts at 0.
int startCol = 0; //col starts at 0
int errorRow = 2; //note errors
int errorCol = 6; //note errors
HSSFRow headerRow = sheet.GetRow(hRowNum);
int colIndex = startCol;
#region headers
//date
sheet.GetRow(0).GetCell(1).SetCellValue(DateTime.Now);
//Title
sheet.GetRow(1).GetCell(colIndex).SetCellValue(data.FirstOrDefault().ProjectName);
// handling headers.
headerRow.GetCell(colIndex).SetCellValue("ID");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Strategy");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Cost");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("ScenarioID");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("CurrentUtility");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("ExpectedUtility");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Raw Benefit");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("wt(Votes)");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("(Normalized) Benefit");
colIndex++;
//headerRow.GetCell(colIndex).SetCellValue("Benefit");
//colIndex++;
#endregion
#region populateData
// foreach (DataColumn column in propertyInfos)
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = hRowNum + 1;
try
{
foreach (var item in data)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex);
if (rowIndex < maxRows - 1)
{
//write each field
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue(item.StrategyID.ToString()); //rank = rec#
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.StrategyName.ToString());
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.StrategyCost.Value);
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.ScenarioID.ToString());
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.CurrentUtility.Value);
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.ExpectedUtility.Value);
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.RawBenefit.Value);
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.wt.Value);
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Benefit1.Value);
colIndex++;
rowIndex++;
}//end if check max rows
#endregion
#region errors
else
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue("Dataset exceeds maximum number of rows");
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Data Exceeds max records");
}
}//end data loop
}// end try
catch
{
colIndex = startCol;
if (sheet.GetRow(errorRow).LastCellNum >= errorCol) //error cell exists
{
sheet.GetRow(errorRow).GetCell(errorCol).SetCellValue("Error Occured");
}
else
{//create cell for error message
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Error Occured");
}
}
#endregion
}
