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();
}