首页 > 代码库 > NOPI操作Excel

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

导出模板

NOPI操作Excel