protected void btnExport_Click(object sender, EventArgs e) { DateTime start = Common.St.ToDateTime(txtStart.Value + " 00:00:00"); DateTime end = Common.St.ToDateTime(txtEnd.Value + " 23:59:59"); var list = DAL.WorkPlanRule.Get(start, end); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_2.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("周上线记录"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; NPOI.SS.UserModel.IRow row = sheet.GetRow(0); NPOI.SS.UserModel.ICell cell = row.GetCell(0); cell.SetCellValue("技术测试周上线记录(" + start.ToString("yyyy年MM月dd日") + "-" + end.ToString("yyyy年MM月dd日") + ")"); // 内容 int i = 2; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(o.SheepNo); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.Project.Name); NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2); cell2.CellStyle = style; cell2.SetCellValue(Common.St.ToDateTimeString(o.PublishTime, "yyyy-MM-dd")); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("每周项目上线记录", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
protected void btnExport_Click(object sender, EventArgs e) { var list = GetList().AsEnumerable().Select(a => new { Name = GetBugzillaUserName(a.Field <int>("reporter")), CC = a.Field <Int64>("cc") }).Where(a => a.Name != ""); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_5.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("bug统计"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; // 内容 int i = 2; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(o.Name); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.CC.ToString()); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("Bug数统计", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public string readCellExcel(string filePath, string isheetname, int irow, int icolumn) { string result = ""; try { using (StreamReader input = new StreamReader(filePath)) { NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream)); if (null == workbook) { result = ""; } NPOI.HSSF.UserModel.HSSFFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook); NPOI.HSSF.UserModel.HSSFDataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN")); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname); NPOI.SS.UserModel.IRow row = sheet.GetRow(irow); if (row != null) { short minColIndex = row.FirstCellNum; short maxColIndex = row.LastCellNum; if (icolumn >= minColIndex || icolumn <= maxColIndex) { NPOI.SS.UserModel.ICell cell = row.GetCell(icolumn); if (cell != null) { result = dataFormatter.FormatCellValue(cell, formulaEvaluator); } } } } } catch (Exception ex) { string test = ex.Message; } return result; }
protected void btnExport_Click(object sender, EventArgs e) { int year = Common.St.ToInt32(selYear.Value); var list = DAL.WorkPlanRule.Get(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_3.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("项目月测试次数"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index; style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index; style.FillPattern = NPOI.SS.UserModel.FillPattern.AltBars; NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle(); style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style2.WrapText = true; style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 内容 int i = 0; int a1, a2, a3, a4, a11 = 0, a22 = 0, a33 = 0, a44 = 0; var userlist = DAL.UserRule.Get(); for (int j = 1; j <= 12; j++) { a3 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).Count(); //上线次数 a4 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).GroupBy(a => a.ProjectId).Count(); //上线项目数 string t = year + "-" + (j + 1) + "-1"; if (j == 12) { t = (year + 1) + "-1-1"; } a1 = list.Where(a => (a.State == 1 && a.RealStartTime < DateTime.Parse(t)) || (a.State == 2 && (!(a.RealStartTime >= DateTime.Parse(t) || a.RealEndTime < DateTime.Parse(year + "-" + j + "-1"))))).Count(); //工单数 项目数 if (year == DateTime.Today.Year && j > DateTime.Today.Month) { a1 = 0; a2 = 0; } else { a2 = userlist.Where(a => a.Status == 1 || a.LeaveTime.Year * 12 + a.LeaveTime.Month > year * 12 + j).Count(); //测试用户数 } //第一行 NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell00 = row0.CreateCell(0); cell00.CellStyle = style; cell00.SetCellValue(GetMonthName(j)); NPOI.SS.UserModel.ICell cell01 = row0.CreateCell(1); cell01.CellStyle = style; NPOI.SS.UserModel.ICell cell02 = row0.CreateCell(2); cell02.CellStyle = style; NPOI.SS.UserModel.ICell cell03 = row0.CreateCell(3); cell03.CellStyle = style; NPOI.SS.UserModel.ICell cell04 = row0.CreateCell(4); cell04.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4)); //第二行 NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell10 = row1.CreateCell(0); cell10.CellStyle = style; cell10.SetCellValue("工单数"); NPOI.SS.UserModel.ICell cell11 = row1.CreateCell(1); cell11.CellStyle = style; cell11.SetCellValue("项目数"); NPOI.SS.UserModel.ICell cell12 = row1.CreateCell(2); cell12.CellStyle = style; cell12.SetCellValue("测试人数"); NPOI.SS.UserModel.ICell cell13 = row1.CreateCell(3); cell13.CellStyle = style; cell13.SetCellValue("上线次数"); NPOI.SS.UserModel.ICell cell14 = row1.CreateCell(4); cell14.CellStyle = style; cell14.SetCellValue("上线项目数"); //第三行 NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell20 = row2.CreateCell(0); cell20.CellStyle = style2; cell20.SetCellValue(a1); NPOI.SS.UserModel.ICell cell21 = row2.CreateCell(1); cell21.CellStyle = style2; cell21.SetCellValue(a1); NPOI.SS.UserModel.ICell cell22 = row2.CreateCell(2); cell22.CellStyle = style2; cell22.SetCellValue(a2); NPOI.SS.UserModel.ICell cell23 = row2.CreateCell(3); cell23.CellStyle = style2; cell23.SetCellValue(a3); NPOI.SS.UserModel.ICell cell24 = row2.CreateCell(4); cell24.CellStyle = style2; cell24.SetCellValue(a4); i++; a11 += a1; a22 += a2; a33 += a3; a44 += a4; } //第一行 NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0); cell40.CellStyle = style; cell40.SetCellValue(year + "年总和统计"); NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1); cell41.CellStyle = style; NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2); cell42.CellStyle = style; NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3); cell43.CellStyle = style; NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4); cell44.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4)); //第二行 NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0); cell50.CellStyle = style; cell50.SetCellValue("工单数"); NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1); cell51.CellStyle = style; cell51.SetCellValue("项目数"); NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2); cell52.CellStyle = style; cell52.SetCellValue("测试人数"); NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3); cell53.CellStyle = style; cell53.SetCellValue("上线次数"); NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4); cell54.CellStyle = style; cell54.SetCellValue("上线项目数"); //第三行 NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell60 = row6.CreateCell(0); cell60.CellStyle = style2; cell60.SetCellValue(a11); NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1); cell61.CellStyle = style2; cell61.SetCellValue(a11); NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2); cell62.CellStyle = style2; cell62.SetCellValue(a22); NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3); cell63.CellStyle = style2; cell63.SetCellValue(a33); NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4); cell64.CellStyle = style2; cell64.SetCellValue(a44); // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("项目月测试次数", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword) { ClearTempTable(); NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Filename, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename); NPOI.HSSF.UserModel.HSSFSheet workSheet = null; //foreach (ExcelLibrary.SpreadSheet.Worksheet tmpWorkSheet in workBook.Worksheets) //{ // if (tmpWorkSheet.Name.Trim().Equals("RosterTable")) // { // workSheet = tmpWorkSheet; // break; // } //} try { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheet("RosterTable"); if (workSheet == null) { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); } } catch { if (workSheet == null) { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); } } if (workSheet.GetRow(ROW_YEAR).GetCell(0).StringCellValue.Trim().StartsWith("Year", StringComparison.CurrentCultureIgnoreCase)) { if (workSheet.GetRow(ROW_YEAR).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { m_importYear = Convert.ToInt32(workSheet.GetRow(ROW_YEAR).GetCell(1).NumericCellValue); } else { errors.addError("Invalid Year", null); } } if (workSheet.GetRow(ROW_MONTH).GetCell(0).StringCellValue.Trim().StartsWith("Month", StringComparison.CurrentCultureIgnoreCase)) { if (workSheet.GetRow(ROW_MONTH).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { m_importMonth = Convert.ToInt32(workSheet.GetRow(ROW_MONTH).GetCell(1).NumericCellValue); } else { errors.addError("Invalid Month", null); } //if (!int.TryParse(workSheet.GetRow(1).GetCell(1).StringCellValue.Trim(), out m_importMonth)) // errors.addError("Invalid Month", null); } if (errors.List.Count > 0) { throw (new HRImportException(errors.Message())); } int intHeaterRow = ROW_CALENDAR_HEADER; int intEmpColumn = 0; ArrayList results = new ArrayList(); int rowCount = 1; NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow); while (intEmpColumn <= headerRow.LastCellNum) { if (headerRow.GetCell(intEmpColumn) != null) { if (headerRow.GetCell(intEmpColumn).StringCellValue.Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase)) { break; } } intEmpColumn++; } if (intEmpColumn > headerRow.LastCellNum) { // do exception } int intEmptyEmpNoCount = 0; try { while (intHeaterRow + rowCount <= workSheet.LastRowNum) { int EmpID = 0; string EmpNo = string.Empty; int colCount = 0; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow + rowCount); if (row == null) { rowCount++; intEmptyEmpNoCount++; continue; } if (row.GetCell(intEmpColumn) == null) { rowCount++; intEmptyEmpNoCount++; continue; } EmpNo = row.GetCell(intEmpColumn).ToString().Trim(); if (string.IsNullOrEmpty(EmpNo)) { rowCount++; intEmptyEmpNoCount++; continue; } intEmptyEmpNoCount = 0; EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID); if (EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else if (EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } colCount = intEmptyEmpNoCount + 1; int lastImportDay = 0; int intCurrentMonth = m_importMonth; int intCurrentYear = m_importYear; while (colCount < headerRow.LastCellNum) { if (headerRow.GetCell(colCount) == null) { if (lastImportDay <= 0) { colCount++; continue; } else { break; } } if (headerRow.GetCell(colCount).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { int day = Convert.ToInt32(headerRow.GetCell(colCount).NumericCellValue); if (day > 0 && day <= DateTime.DaysInMonth(intCurrentYear, intCurrentMonth)) { if (lastImportDay > day) { intCurrentMonth++; if (intCurrentMonth > 12) { intCurrentMonth = 1; intCurrentYear++; } } lastImportDay = day; string RosterCode; if (row.GetCell(colCount) != null) { RosterCode = row.GetCell(colCount).ToString().Trim(); } else { RosterCode = string.Empty; } EUploadRosterTable uploadRosterTable = new EUploadRosterTable(); uploadRosterTable.EmpID = EmpID; uploadRosterTable.RosterTableDate = new DateTime(intCurrentYear, intCurrentMonth, day); DateTime inTime, outTime; uploadRosterTable.RosterCodeID = Import.Parse.GetRosterCodeID(dbConn, RosterCode, out inTime, out outTime); if (!inTime.Ticks.Equals(0)) { uploadRosterTable.RosterTableOverrideInTime = inTime; } if (!outTime.Ticks.Equals(0)) { uploadRosterTable.RosterTableOverrideOutTime = outTime; } uploadRosterTable.SessionID = m_SessionID; uploadRosterTable.TransactionDate = UploadDateTime; uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode)) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() }); } else { EUploadRosterTable.db.insert(dbConn, uploadRosterTable); } } } colCount++; } rowCount++; } } catch (Exception e) { errors.addError(e.Message, null); } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(errors.Message())); } return(GetImportDataFromTempDatabase(null)); //org.in2bits.MyXls.XlsDocument xlsDoc = new org.in2bits.MyXls.XlsDocument(Filename); //org.in2bits.MyXls.Worksheet workSheet; //try //{ // workSheet = xlsDoc.Workbook.Worksheets["RosterTable"]; //} //catch //{ // workSheet = xlsDoc.Workbook.Worksheets[0]; //} //if (workSheet.Rows[1].GetCell(1).Value.ToString().Trim().Equals("Year", StringComparison.CurrentCultureIgnoreCase)) //{ // if (!int.TryParse(workSheet.Rows[1].CellAtCol(2).Value.ToString().Trim(), out m_importYear)) // errors.addError("Invalid Year", null); //} //if (workSheet.Rows[2].GetCell(1).Value.ToString().Trim().Equals("Month", StringComparison.CurrentCultureIgnoreCase)) //{ // if (!int.TryParse(workSheet.Rows[2].CellAtCol(2).Value.ToString().Trim(), out m_importMonth)) // errors.addError("Invalid Month", null); //} //if (errors.List.Count > 0) //{ // throw (new HRImportException(errors.Message())); //} //ushort intHeaderRow = 3; //ArrayList results = new ArrayList(); //ushort rowCount = 1; //try //{ // while (intHeaderRow + rowCount <= workSheet.Rows.MaxRow) // { // int EmpID = 0; // string EmpNo = string.Empty; // ushort colCount = 1; // org.in2bits.MyXls.Row row = workSheet.Rows[(ushort)(intHeaderRow + rowCount)]; // while (colCount <= row.MaxCellCol) // { // if (workSheet.Rows[intHeaderRow].CellAtCol(colCount).Value.ToString().Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase)) // { // EmpNo = row.CellAtCol(colCount).Value.ToString().Trim(); // EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID); // if (EmpID <= 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); // break; // } // colCount++; // } // colCount = 1; // while (colCount <= workSheet.Rows[(ushort)(intHeaderRow + rowCount)].MaxCellCol) // { // if (workSheet.Cells .Rows[(ushort)(intHeaderRow)].CellExists(colCount)) // { // string RosterCode = string.Empty; // if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellExists(colCount)) // if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellAtCol(colCount).Value != null) // RosterCode = row.CellAtCol(colCount).Value.ToString().Trim(); // int day = 0; // if (int.TryParse(workSheet.Rows[intHeaderRow].GetCell(colCount).Value.ToString().Trim(), out day)) // { // if (day > 0 && day <= DateTime.DaysInMonth(m_importYear, m_importMonth)) // { // EUploadRosterTable uploadRosterTable = new EUploadRosterTable(); // uploadRosterTable.EmpID = EmpID; // uploadRosterTable.RosterTableDate = new DateTime(m_importYear, m_importMonth, day); // uploadRosterTable.RosterCodeID = Import.Parse.*ID(dbConn, RosterCode); // uploadRosterTable.SessionID = m_SessionID; // uploadRosterTable.TransactionDate = UploadDateTime; // uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; // if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode)) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() }); // } // else // EUploadRosterTable.db.insert(dbConn, uploadRosterTable); // } // } // } // colCount++; // } // rowCount++; // } //} //catch (Exception e) //{ // errors.addError(e.Message, null); //} //if (errors.List.Count > 0) //{ // ClearTempTable(); // throw (new HRImportException(errors.Message())); //} //return GetImportDataFromTempDatabase(null); }
private static DataTable Convert(string filePath) { using (FileStream stream = new FileStream(ServerHelper.MapPath(filePath), FileMode.Open)) { string sheetName = "Sheet1"; DataTable data = new DataTable(); ISheet sheet = null; int startRow = 0; try { IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { dataRow[j] = row.GetCell(j).ToString(); } } data.Rows.Add(dataRow); } } return(data); } catch (Exception ex) { return(null); } } }
private void btnExcel_Click(object sender, EventArgs e) { try { IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new FileStream(Application.StartupPath + "\\office\\财务统计模板.xls", FileMode.Open, FileAccess.Read)); //IWorkbook mWorkbook = //ISheet mSheet = mWorkbook.CreateSheet("Sheet1"); ISheet mSheet = mWorkbook.GetSheet("采购明细表"); ICellStyle styleRight = mWorkbook.CreateCellStyle(); //ICellStyle NumberStyle = mWorkbook.CreateCellStyle(); //IDataFormat format = mWorkbook.CreateDataFormat(); styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //NumberStyle.DataFormat = format.GetFormat("0.00"); ICell mCell; int i, j; DataGridView mView = dgvDaPurchaseContract; //添加标头 IRow mRow;//= mSheet.CreateRow(0); //for (i = 0; i <= mView.Columns.Count - 1; i++) //{ // mCell = mRow.CreateCell(i); // mCell.SetCellValue(mView.Columns[i].HeaderText); // mCell.CellStyle = styleRight; //} //添加内容 for (i = 1; i <= mView.RowCount; i++) { mRow = mSheet.CreateRow(i + 2); for (j = 0; j <= mView.Columns.Count - 1; j++) { if (mView.Columns[j].Visible == false) { continue; } mCell = mRow.CreateCell(j); if (Classes.PubFunc.IsNumber(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)) == true && j != dpc单据号码.Index) { mCell.SetCellValue(Convert.ToDouble(mView.Rows[i - 1].Cells[j].Value)); } else { mCell.SetCellValue(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)); } mCell.CellStyle = styleRight; } } //创建文件 SaveFileDialog mDialog = new SaveFileDialog(); mDialog.AddExtension = true; mDialog.DefaultExt = "xls"; mDialog.Filter = "Excel Worksheets(*.xls)|*.xls"; mDialog.InitialDirectory = System.Environment.CurrentDirectory; mDialog.FileName = "财务采购统计-" + DateTime.Now.ToString("yyyyMMddhhmmss"); if (mDialog.ShowDialog() == DialogResult.OK) { Stream mFile = mDialog.OpenFile(); mWorkbook.Write(mFile); mFile.Close(); MessageBox.Show("保存成功!", "提示"); } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } }
protected void btnExport_Click(object sender, EventArgs e) { int year = Common.St.ToInt32(selYear.Value); var m = 12; if (year == DateTime.Today.Year) { m = DateTime.Today.Month; } var list = DAL.WorkPlanRule.Get().Where(a => a.NeederId > 0 && a.Needer.Status == 1).GroupBy(a => a.NeederId).Select(a => { return(new { Name = a.First().Needer.RealName, M1 = m < 1 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-2-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-2-1") || b.RealEndTime < DateTime.Parse(year + "-1-1"))))).Count(), M2 = m < 2 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-3-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-3-1") || b.RealEndTime < DateTime.Parse(year + "-2-1"))))).Count(), M3 = m < 3 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-4-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-4-1") || b.RealEndTime < DateTime.Parse(year + "-3-1"))))).Count(), M4 = m < 4 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-5-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-5-1") || b.RealEndTime < DateTime.Parse(year + "-4-1"))))).Count(), M5 = m < 5 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-6-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-6-1") || b.RealEndTime < DateTime.Parse(year + "-5-1"))))).Count(), M6 = m < 6 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-7-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-7-1") || b.RealEndTime < DateTime.Parse(year + "-6-1"))))).Count(), M7 = m < 7 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-8-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-8-1") || b.RealEndTime < DateTime.Parse(year + "-7-1"))))).Count(), M8 = m < 8 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-9-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-9-1") || b.RealEndTime < DateTime.Parse(year + "-8-1"))))).Count(), M9 = m < 9 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-10-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-10-1") || b.RealEndTime < DateTime.Parse(year + "-9-1"))))).Count(), M10 = m < 10 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-11-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-11-1") || b.RealEndTime < DateTime.Parse(year + "-10-1"))))).Count(), M11 = m < 11 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-12-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-12-1") || b.RealEndTime < DateTime.Parse(year + "-11-1"))))).Count(), M12 = m < 12 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse((year + 1) + "-1-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse((year + 1) + "-1-1") || b.RealEndTime < DateTime.Parse(year + "-12-1"))))).Count() }); }); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_4.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("测试人员负责项目数统计"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 内容 int i = 1; foreach (var o in list) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(o.Name); NPOI.SS.UserModel.ICell cell1 = row.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.M1); NPOI.SS.UserModel.ICell cell2 = row.CreateCell(2); cell2.CellStyle = style; cell2.SetCellValue(o.M2); NPOI.SS.UserModel.ICell cell3 = row.CreateCell(3); cell3.CellStyle = style; cell3.SetCellValue(o.M3); NPOI.SS.UserModel.ICell cell4 = row.CreateCell(4); cell4.CellStyle = style; cell4.SetCellValue(o.M4); NPOI.SS.UserModel.ICell cell5 = row.CreateCell(5); cell5.CellStyle = style; cell5.SetCellValue(o.M5); NPOI.SS.UserModel.ICell cell6 = row.CreateCell(6); cell6.CellStyle = style; cell6.SetCellValue(o.M6); NPOI.SS.UserModel.ICell cell7 = row.CreateCell(7); cell7.CellStyle = style; cell7.SetCellValue(o.M7); NPOI.SS.UserModel.ICell cell8 = row.CreateCell(8); cell8.CellStyle = style; cell8.SetCellValue(o.M8); NPOI.SS.UserModel.ICell cell9 = row.CreateCell(9); cell9.CellStyle = style; cell9.SetCellValue(o.M9); NPOI.SS.UserModel.ICell cell10 = row.CreateCell(10); cell10.CellStyle = style; cell10.SetCellValue(o.M10); NPOI.SS.UserModel.ICell cell11 = row.CreateCell(11); cell11.CellStyle = style; cell11.SetCellValue(o.M11); NPOI.SS.UserModel.ICell cell12 = row.CreateCell(12); cell12.CellStyle = style; cell12.SetCellValue(o.M12); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("测试人员负责项目数统计", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public string readCellExcel(string filePath, string isheetname, int irow, int icolumn) { string result = ""; try { using (StreamReader input = new StreamReader(filePath)) { NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream)); if (null == workbook) { result = ""; } NPOI.SS.UserModel.IFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook); NPOI.SS.UserModel.DataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN")); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname); NPOI.SS.UserModel.IRow row = sheet.GetRow(irow); result = dataFormatter.FormatCellValue(row.Cells[icolumn], formulaEvaluator); /*foreach (NPOI.SS.UserModel.ISheet sheet in workbook) { foreach (NPOI.SS.UserModel.IRow row in sheet) { foreach (NPOI.SS.UserModel.ICell cell in row) { string value = dataFormatter.FormatCellValue(cell, formulaEvaluator); } } }*/ } } catch { } return result; }
protected void btnExport_Click(object sender, EventArgs e) { var list = DAL.WorkPlanRule.Get().Where(a => a.PublishTime.Year == Common.St.ToInt32(selYear.Value)).Select(a => { int pid = 0, id = 0; string pname = "", name = ""; GetIDAndName(a.Project, ref pid, ref id, ref pname, ref name); return(new { ID_0 = pid, ID_1 = id, Name_0 = pname, Name_1 = name, M = a.PublishTime.Month, PublishTime = a.PublishTime, Name_3 = a.Project.Name }); }).GroupBy(a => a.ID_1).Select(a => { return(new { PName = a.First().Name_0, Name = a.First().Name_1, M1 = a.Where(b => b.M == 1).Count(), M2 = a.Where(b => b.M == 2).Count(), M3 = a.Where(b => b.M == 3).Count(), M4 = a.Where(b => b.M == 4).Count(), M5 = a.Where(b => b.M == 5).Count(), M6 = a.Where(b => b.M == 6).Count(), M7 = a.Where(b => b.M == 7).Count(), M8 = a.Where(b => b.M == 8).Count(), M9 = a.Where(b => b.M == 9).Count(), M10 = a.Where(b => b.M == 10).Count(), M11 = a.Where(b => b.M == 11).Count(), M12 = a.Where(b => b.M == 12).Count(), S1 = string.Join("\n", a.Where(b => b.M == 1).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S2 = string.Join("\n", a.Where(b => b.M == 2).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S3 = string.Join("\n", a.Where(b => b.M == 3).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S4 = string.Join("\n", a.Where(b => b.M == 4).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S5 = string.Join("\n", a.Where(b => b.M == 5).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S6 = string.Join("\n", a.Where(b => b.M == 6).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S7 = string.Join("\n", a.Where(b => b.M == 7).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S8 = string.Join("\n", a.Where(b => b.M == 8).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S9 = string.Join("\n", a.Where(b => b.M == 9).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S10 = string.Join("\n", a.Where(b => b.M == 10).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S11 = string.Join("\n", a.Where(b => b.M == 11).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()), S12 = string.Join("\n", a.Where(b => b.M == 12).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()) }); }).OrderBy(a => a.PName).Where(a => (txtProjectParent.Value.Trim() == "" || a.PName.IndexOf(txtProjectParent.Value.Trim()) >= 0) && (txtProject.Value.Trim() == "" || a.Name.IndexOf(txtProject.Value.Trim()) >= 0)); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_project.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("科教组项目"); // 内容 int i = 1; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.SetCellValue(o.PName); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.SetCellValue(o.Name); NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2); cell2.SetCellValue(o.M1); NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3); cell3.SetCellValue(o.M2); NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4); cell4.SetCellValue(o.M3); NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5); cell5.SetCellValue(o.M4); NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6); cell6.SetCellValue(o.M5); NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7); cell7.SetCellValue(o.M6); NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8); cell8.SetCellValue(o.M7); NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9); cell9.SetCellValue(o.M8); NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10); cell10.SetCellValue(o.M9); NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11); cell11.SetCellValue(o.M10); NPOI.SS.UserModel.ICell cell12 = row2.CreateCell(12); cell12.SetCellValue(o.M11); NPOI.SS.UserModel.ICell cell13 = row2.CreateCell(13); cell13.SetCellValue(o.M12); i++; } NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(i); row3.CreateCell(0).SetCellValue("总计"); row3.CreateCell(1).SetCellValue(list.Sum(a => a.M1 + a.M2 + a.M3 + a.M4 + a.M5 + a.M6 + a.M7 + a.M8 + a.M9 + a.M10 + a.M11 + a.M12)); row3.CreateCell(2).SetCellValue(list.Sum(a => a.M1)); row3.CreateCell(3).SetCellValue(list.Sum(a => a.M2)); row3.CreateCell(4).SetCellValue(list.Sum(a => a.M3)); row3.CreateCell(5).SetCellValue(list.Sum(a => a.M4)); row3.CreateCell(6).SetCellValue(list.Sum(a => a.M5)); row3.CreateCell(7).SetCellValue(list.Sum(a => a.M6)); row3.CreateCell(8).SetCellValue(list.Sum(a => a.M7)); row3.CreateCell(9).SetCellValue(list.Sum(a => a.M8)); row3.CreateCell(10).SetCellValue(list.Sum(a => a.M9)); row3.CreateCell(11).SetCellValue(list.Sum(a => a.M10)); row3.CreateCell(12).SetCellValue(list.Sum(a => a.M11)); row3.CreateCell(13).SetCellValue(list.Sum(a => a.M12)); NPOI.SS.UserModel.ISheet sheet2 = book.GetSheet("上线明细表"); // 内容 int j = 1; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet2.CreateRow(j); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.SetCellValue(o.Name); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.SetCellValue(o.S1); NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2); cell2.SetCellValue(o.S2); NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3); cell3.SetCellValue(o.S3); NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4); cell4.SetCellValue(o.S4); NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5); cell5.SetCellValue(o.S5); NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6); cell6.SetCellValue(o.S6); NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7); cell7.SetCellValue(o.S7); NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8); cell8.SetCellValue(o.S8); NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9); cell9.SetCellValue(o.S9); NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10); cell10.SetCellValue(o.S10); NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11); cell11.SetCellValue(o.S11); NPOI.SS.UserModel.ICell cell12 = row2.CreateCell(12); cell12.SetCellValue(o.S12); j++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode(selYear.Value + "年项目上线频度表"), System.Text.Encoding.UTF8)); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
protected void btnExport_Click(object sender, EventArgs e) { //http://www.itnose.net/detail/476834.html //http://blog.csdn.net/gjban/article/details/39030669 var list = GetData(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_workplan.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("工作计划表"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; // 内容 int i = 3; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(i - 2); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.SheepNo); NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2); cell2.CellStyle = style; cell2.SetCellValue(o.Project.Name); NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3); cell3.CellStyle = style; cell3.SetCellValue(o.WorkRemark); NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4); cell4.CellStyle = style; cell4.SetCellValue(o.PlanTypeStr); NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5); cell5.CellStyle = style; cell5.SetCellValue(Common.St.ToDateTimeString(o.StartTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6); cell6.CellStyle = style; cell6.SetCellValue(Common.St.ToDateTimeString(o.RealStartTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7); cell7.CellStyle = style; cell7.SetCellValue(Common.St.ToDateTimeString(o.EndTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8); cell8.CellStyle = style; cell8.SetCellValue(Common.St.ToDateTimeString(o.RealEndTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9); cell9.CellStyle = style; cell9.SetCellValue(o.StateStr); NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10); cell10.CellStyle = style; cell10.SetCellValue(o.Needer.RealName); NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11); cell11.CellStyle = style; cell11.SetCellValue(o.Remark); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("工作计划表", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }