NOPI操作Excel
using System.Collections.Generic; using System.Data; using System.Text; using System.Data.SqlClient; using Common; using BusinessLogic.Entity; using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using System.Web; namespace BusinessLogic.LiquidationManage { /// <summary> /// 毕江 年计划后台逻辑 /// </summary> public class YearPlanLogic { private static object syncLock = new object(); private static YearPlanLogic _instance; public static YearPlanLogic Instance() //单例模式 { if (_instance == null) { lock (syncLock) { if (_instance == null) { _instance = new YearPlanLogic(); } } } return _instance; } /// <summary> /// 初始化年计划数据 /// </summary> /// <param name="userId"></param> /// <param name="year"></param> /// <returns></returns> public string InitYearPlan(string userId,string year,string month) { SqlParameter[] paras ={ new SqlParameter("@UserID",userId), new SqlParameter("@Year",year), new SqlParameter("@Month",month), new SqlParameter("@return","") }; return SqlHelp.Instance().ExecuteProduceReturn("[Pro_InitYearPlan]", paras); } /// <summary> /// 获取年清算数据 /// </summary> /// <param name="userId"></param> /// <param name="year"></param> /// <param name="isAll">1:获取所有的</param> /// <returns></returns> public DataTable GetYearPlan(string userId, string year, string isAll) { SqlParameter[] paras ={ new SqlParameter("@UserID",userId), new SqlParameter("@Year",year), new SqlParameter("@IsAll",isAll) }; return SqlHelp.Instance().GetProduceDataTable("[Pro_GetYearPlan]", CommandType.StoredProcedure, paras); } /// <summary> /// 领导查询年计划数据 /// </summary> /// <param name="year"></param> /// <returns></returns> public DataTable SerchYearPlanData(string year) { SqlParameter[] paras ={ new SqlParameter("@Year",year) }; return SqlHelp.Instance().GetProduceDataTable("[Pro_SerchYearPlanData]", CommandType.StoredProcedure, paras); } /// <summary> /// 更新年计划 /// </summary> /// <param name="qs"></param> /// <returns></returns> public string UpdateYearPlanData(YearPlan qs) { SqlParameter[] paras ={ new SqlParameter("@ID",qs.ID), new SqlParameter("@PJID",qs.PJID), new SqlParameter("@YEAR",qs.QsYear), new SqlParameter("@FIXEDFEE",qs.FixedFee), new SqlParameter("@FEEXS",qs.FeeXs), new SqlParameter("@PREPROPORTION",qs.PreProportion), new SqlParameter("@QSXS",qs.QSXS), new SqlParameter("@JJXS",qs.JJXS), new SqlParameter("@QSGRXS",qs.QSGRXS), new SqlParameter("@NOTES",qs.Notes), new SqlParameter("@FLAG",qs.FLAG), new SqlParameter("@return","") }; return SqlHelp.Instance().ExecuteProduceReturn("[Pro_UpdateYearPlan]", paras); } /// <summary> /// 完成阶段年计划 /// </summary> /// <param name="idStr"></param> /// <returns></returns> public string FinishYearPlan(string idStr,string year) { SqlParameter[] paras ={ new SqlParameter("@IDStr",idStr), new SqlParameter("@Year",year), new SqlParameter("@return","") }; return SqlHelp.Instance().ExecuteProduceReturn("[Pro_FinishYearPlan]", paras); } /// <summary> /// 重启年计划 /// </summary> /// <param name="stageIdStr"></param> /// <returns></returns> public string RestartYearPlan(string stageIdStr) { SqlParameter[] paras ={ new SqlParameter("@IDStr",stageIdStr), new SqlParameter("@return","") }; return SqlHelp.Instance().ExecuteProduceReturn("[Pro_RestartYearPlan]", paras); } /// <summary> /// 获取项目类型下拉数据 /// </summary> /// <returns></returns> public DataTable GetPjType() { string sql = "select ItemId ID,ItemName NAME from [SYS_DATABOOK] where FatherId='U_PROJECT_TYPE' order by [DisplayOrder]"; return SqlHelp.Instance().ExecuteDataTable(sql); } /// <summary> /// 获取用户导出Excel数据 /// </summary> /// <returns></returns> public DataTable GetUserExportYearPlanData(string userId, string year, string isAll) { SqlParameter[] paras ={ new SqlParameter("@UserID",userId), new SqlParameter("@Year",year), new SqlParameter("@IsAll",isAll) }; return SqlHelp.Instance().GetProduceDataTable("[Pro_GetUserExportYearPlanData]", CommandType.StoredProcedure, paras); } /// <summary> /// 获取年计划导出Excel数据 /// </summary> /// <returns></returns> public DataTable GetAllExportYearPlanData(string year) { SqlParameter[] paras ={ new SqlParameter("@Year",year) }; return SqlHelp.Instance().GetProduceDataTable("[Pro_GetAllExportYearPlanData]", CommandType.StoredProcedure, paras); } /// <summary> /// 导出用户项目年计划数据 /// </summary> /// <param name="userId"></param> /// <param name="year"></param> /// <param name="isAll"></param> public void ExportYearPlan(string userId, string year, string isAll) { DataTable dt = GetUserExportYearPlanData(userId, year, isAll);//获取数据 using ( FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Files/Templetes/年计划导出模板.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheetAt(0); hssfworkbook.SetSheetName(0, year + "年度项目年计划"); int startRow = 3; //数据开始行 int count = 0; int megCount = 0; //List<int> merg = new List<int>(); Dictionary<int, int> dicMeg = new Dictionary<int, int>(); string pjType = ""; string pjId = ""; IRow rowType = null; IRow rowData = null; IRow row = sheet.GetRow(0); row.GetCell(0).SetCellValue(year + "年度项目年计划"); for (int i = 0; i < dt.Rows.Count; i++)//循环数据 { if (pjType != dt.Rows[i]["PJTYPENAME"].ToString()) { count++; rowType = rowType == null ? sheet.GetRow(2) : CopyRow(hssfworkbook, sheet, 2, 1 + i + count); pjType = dt.Rows[i]["PJTYPENAME"].ToString(); rowType.GetCell(0).SetCellValue(pjType); if (pjId != dt.Rows[i]["PJID"].ToString()) { if (megCount != 0) { dicMeg.Add(1 + i + count - megCount, i + count); megCount = 0; } pjId = dt.Rows[i]["PJID"].ToString(); } } else { if (pjId != dt.Rows[i]["PJID"].ToString()) { if (megCount != 0) { dicMeg.Add(2 + i + count - megCount, 1 + i + count); megCount = 0; } pjId = dt.Rows[i]["PJID"].ToString(); } } rowData = i == 0 ? sheet.GetRow(startRow) : CopyRow(hssfworkbook, sheet, startRow, startRow + i + count - 1); rowData.GetCell(0).SetCellValue(dt.Rows[i]["PJNAME"].ToString()); rowData.GetCell(1).SetCellValue(dt.Rows[i]["USERNAME"].ToString()); rowData.GetCell(2).SetCellValue(dt.Rows[i]["STAGENAME"].ToString()); rowData.GetCell(3).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["FIXEDFEE"])); rowData.GetCell(4).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PREQSWORKDAY"])); rowData.GetCell(5).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PREPROPORTION"])); rowData.GetCell(6).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PREFINISHWORKDAY"])); rowData.GetCell(7).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PJPREQSOUTPUT"])); rowData.GetCell(8).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["YEARPREQSOUTPUT"])); rowData.GetCell(9).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PJPREMONEY"])); rowData.GetCell(10).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["YEARPJPREMONEY"])); rowData.GetCell(11).SetCellValue(dt.Rows[i]["NOTES"].ToString()); megCount++; if (i == dt.Rows.Count - 1) { dicMeg.Add(3 + i + count - 2 - megCount + 2, 3 + i + count - 1); } } foreach (KeyValuePair<int, int> dic in dicMeg) { CellRangeAddress cellRangeAddress = new CellRangeAddress(dic.Key, dic.Value, 0, 0); sheet.AddMergedRegion(cellRangeAddress); } MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); HttpContext curContext = HttpContext.Current; curContext.Response.Clear(); curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(year + "年度项目年计划", System.Text.Encoding.UTF8))); curContext.Response.BinaryWrite(ms.ToArray()); curContext.ApplicationInstance.CompleteRequest(); //curContext.Response.End(); hssfworkbook = null; ms.Close(); ms.Dispose(); } } /// <summary> /// 导出所有项目年计划数据 /// </summary> /// <param name="year"></param> public void ExportAllYearPlan(string year) { DataTable dt = GetAllExportYearPlanData(year);//获取数据 using ( FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Files/Templetes/年计划导出模板.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheetAt(0); hssfworkbook.SetSheetName(0, year + "年度项目年计划汇总"); int startRow = 3; //数据开始行 int count = 0; int megCount = 0; // List<int> merg = new List<int>(); Dictionary<int, int> dicMeg = new Dictionary<int, int>(); string pjType = ""; string pjId = ""; IRow rowType = null; IRow rowData = null; IRow row = sheet.GetRow(0); row.GetCell(0).SetCellValue(year + "年度项目年计划汇总"); for (int i = 0; i < dt.Rows.Count; i++)//循环数据 { if (pjType != dt.Rows[i]["PJTYPENAME"].ToString()) { count++; rowType = rowType == null ? sheet.GetRow(2) : CopyRow(hssfworkbook, sheet, 2, 1 + i + count); pjType = dt.Rows[i]["PJTYPENAME"].ToString(); rowType.GetCell(0).SetCellValue(pjType); if (pjId != dt.Rows[i]["PJID"].ToString()) { if (megCount != 0) { dicMeg.Add(1 + i + count - megCount, i + count); megCount = 0; } pjId = dt.Rows[i]["PJID"].ToString(); } } else { if (pjId != dt.Rows[i]["PJID"].ToString()) { if (megCount != 0) { dicMeg.Add(2 + i + count - megCount, 1 + i + count); megCount = 0; } pjId = dt.Rows[i]["PJID"].ToString(); } } rowData = i == 0 ? sheet.GetRow(startRow) : CopyRow(hssfworkbook, sheet, startRow, startRow + i + count - 1); rowData.GetCell(0).SetCellValue(dt.Rows[i]["PJNAME"].ToString()); rowData.GetCell(1).SetCellValue(dt.Rows[i]["USERNAME"].ToString()); rowData.GetCell(2).SetCellValue(dt.Rows[i]["STAGENAME"].ToString()); rowData.GetCell(3).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["FIXEDFEE"])); rowData.GetCell(4).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PREQSWORKDAY"])); rowData.GetCell(5).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PREPROPORTION"])); rowData.GetCell(6).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PREFINISHWORKDAY"])); rowData.GetCell(7).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PJPREQSOUTPUT"])); rowData.GetCell(8).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["YEARPREQSOUTPUT"])); rowData.GetCell(9).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["PJPREMONEY"])); rowData.GetCell(10).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["YEARPJPREMONEY"])); rowData.GetCell(11).SetCellValue(dt.Rows[i]["NOTES"].ToString()); megCount++; if (i == dt.Rows.Count - 1) { dicMeg.Add(3 + i + count - 2 - megCount + 2, 3 + i + count - 1); } } foreach (KeyValuePair<int, int> dic in dicMeg) { CellRangeAddress cellRangeAddress = new CellRangeAddress(dic.Key, dic.Value, 0, 0); sheet.AddMergedRegion(cellRangeAddress); } MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); HttpContext curContext = HttpContext.Current; curContext.Response.Clear(); curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(year + "年度项目年计划汇总", System.Text.Encoding.UTF8))); curContext.Response.BinaryWrite(ms.ToArray()); curContext.ApplicationInstance.CompleteRequest(); //curContext.Response.End(); hssfworkbook = null; ms.Close(); ms.Dispose(); } } /// <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 IRow CopyRow(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row IRow newRow = worksheet.CreateRow(destinationRowNum); IRow sourceRow = 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 = worksheet.CreateRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.LastCellNum; i++) { // Grab a copy of the old/new cell ICell oldCell = sourceRow.GetCell(i); ICell newCell = newRow.CreateCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell ICellStyle newCellStyle = workbook.CreateCellStyle(); newCellStyle.CloneStyleFrom(oldCell.CellStyle); ; newCell.CellStyle = newCellStyle; newCell.SetCellType(oldCell.CellType); // 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.SetCellFormula(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 (int i = 0; i < worksheet.NumMergedRegions; i++) { CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i); if (cellRangeAddress.FirstRow == sourceRow.RowNum) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum, (newRow.RowNum + (cellRangeAddress.FirstRow - cellRangeAddress.LastRow)), cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn); worksheet.AddMergedRegion(newCellRangeAddress); } } worksheet.ForceFormulaRecalculation = true; newRow.Height = sourceRow.Height; return newRow; } } }
导出模板
