Example #1
0
        public static Stream RenderDataTableToExcel(string[] headers, DataTable SourceTable)
        {
            MemoryStream ms = new MemoryStream();

            NPOI.HSSF.UserModel.HSSFWorkbook workbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet     = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow           headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                headerRow.CreateCell(i).SetCellValue(headers[i]);
            }
            int rowIndex = 1;

            foreach (DataRow row in SourceTable.Rows)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.CreateCell(0).SetCellValue(row["ItemName"].ToString());
                dataRow.CreateCell(1).SetCellValue(row["Count"].ToString());
                dataRow.CreateCell(2).SetCellValue(row["Percent"].ToString());
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet       = null;
            headerRow   = null;
            workbook    = null;
            return(ms);
        }
Example #2
0
        /// <summary>
        /// 批量导出本校第一批派位学生
        /// </summary>
        /// <returns></returns>
        public FileResult ExportStu2()
        {
            // schoolname = "401";
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            List <Staff> listRainInfo = db.Staffs.ToList();

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("电脑号");
            row1.CreateCell(1).SetCellValue("姓名");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < listRainInfo.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].StaffNumber.ToString());
                rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].Name.ToString());
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls"));
        }
        public static HSSFWorkbook CreateExecl(List <string> title, List <AppUserPerson> list)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            //row1.RowStyle.FillBackgroundColor = "";
            int i = 0;

            foreach (var item in title)
            {
                row1.CreateCell(i).SetCellValue(item);
                i++;
            }
            //将数据逐步写入sheet1各个行
            for (int j = 0; j < list.Count; j++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
                rowtemp.CreateCell(0).SetCellValue(list[j].adnm);
                rowtemp.CreateCell(1).SetCellValue(list[j].userName);
                rowtemp.CreateCell(2).SetCellValue(list[j].phone);
            }
            return(book);
        }
Example #4
0
        void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
        {
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

            // 添加表头
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            int index = 0;

            foreach (DataColumn item in dt.Columns)
            {
                NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue(item.Caption);
                index++;
            }

            // 添加数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                index = 0;
                row   = sheet.CreateRow(i + 1);
                foreach (DataColumn item in dt.Columns)
                {
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(dt.Rows[i][item].ToString());
                    index++;
                }
            }
        }
        private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet)
        {
            SetCellRangeAddress(sheet, 0, 0, 0, 1);
            SetCellRangeAddress(sheet, 0, 0, 3, 4);
            SetCellRangeAddress(sheet, 0, 0, 6, 7);
            SetCellRangeAddress(sheet, 0, 0, 9, 10);
            SetCellRangeAddress(sheet, 0, 0, 12, 14);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("输入功率标定");
            row.CreateCell(3).SetCellValue("输出功率标定");
            row.CreateCell(6).SetCellValue("反射功率标定");
            row.CreateCell(9).SetCellValue("ALC功率标定");
            row.CreateCell(12).SetCellValue("衰减补偿");
            row.GetCell(0).CellStyle  = GetCellStyle(book);
            row.GetCell(3).CellStyle  = GetCellStyle(book);
            row.GetCell(6).CellStyle  = GetCellStyle(book);
            row.GetCell(9).CellStyle  = GetCellStyle(book);
            row.GetCell(12).CellStyle = GetCellStyle(book);

            row = sheet.CreateRow(1);
            for (int i = 0; i < 4; i++)
            {
                row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压");
                row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点");
                row.GetCell(3 * i).CellStyle     = GetCellStyle(book);
                row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book);
            }
            row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值");
            row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值");
            row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值");
            row.GetCell(12).CellStyle = GetCellStyle(book);
            row.GetCell(13).CellStyle = GetCellStyle(book);
            row.GetCell(14).CellStyle = GetCellStyle(book);
        }
Example #6
0
        public IActionResult ExportExcel(string keyword = "")
        {
            DataAccess.TASM_MACHINEManager manager = new DataAccess.TASM_MACHINEManager();


            List <MachineModel> list = new List <MachineModel>();

            manager.ListByWhere(keyword, ref list);

            HSSFWorkbook excelBook = new HSSFWorkbook(); //创建工作簿Excel
            ISheet       sheet1    = excelBook.CreateSheet("项目履历表");

            IRow row1 = sheet1.CreateRow(0);

            row1.CreateCell(0).SetCellValue("设备名称");
            row1.CreateCell(1).SetCellValue("设备类型");

            row1.CreateCell(2).SetCellValue("设备序号");
            row1.CreateCell(3).SetCellValue("客户名称");
            row1.CreateCell(4).SetCellValue("合同编号");
            row1.CreateCell(5).SetCellValue("订单时间");

            row1.CreateCell(6).SetCellValue("送货时间");
            row1.CreateCell(7).SetCellValue("送货单号");
            row1.CreateCell(8).SetCellValue("验收时间");

            row1.CreateCell(9).SetCellValue("创建时间");



            for (int i = 0; i < list.Count(); i++)
            {
                NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);

                rowTemp.CreateCell(0).SetCellValue(list[i].NAME);
                rowTemp.CreateCell(1).SetCellValue(list[i].TYPESNAME);

                rowTemp.CreateCell(2).SetCellValue(list[i].SERIAL);
                rowTemp.CreateCell(3).SetCellValue(list[i].CUSTOMER);
                rowTemp.CreateCell(4).SetCellValue(list[i].CONTRACT);
                rowTemp.CreateCell(5).SetCellValue(list[i].ORDERTIME.Value.ToString("yyyy-MM-dd"));
                rowTemp.CreateCell(6).SetCellValue(list[i].DELIVERYTIME.Value);
                rowTemp.CreateCell(7).SetCellValue(list[i].DELIVERYNUMBER);
                rowTemp.CreateCell(8).SetCellValue(list[i].CHECKTIME.Value);
                rowTemp.CreateCell(9).SetCellValue(list[i].CREATETIME.Value);
            }

            var fileName = "设备台账" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";//文件名

            //将Excel表格转化为流,输出

            MemoryStream bookStream = new MemoryStream();

            excelBook.Write(bookStream);
            bookStream.Seek(0, SeekOrigin.Begin);
            return(File(bookStream, "application/vnd.ms-excel", fileName));
        }
Example #7
0
        //导出 dt,和表头都传
        public Stream ExportExcel(DataTable dt, List <EColName> list)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);



            //组建表头
            int index = 0;

            foreach (var i in list)
            {
                row1.CreateCell(index).SetCellValue(i.col);
                i.sortseq = index;
                index     = index + 1;
            }

            //将数据逐步写入sheet1各个行
            double d0 = 0;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);

                for (int jj = 0; jj < list.Count; jj++)
                {
                    string colname = (from EColName a in list where a.sortseq == jj select a.colname).ToArray()[0];
                    string coldata = dt.Rows[i][colname].ToString();
                    string dbtype  = dt.Columns[colname].DataType.ToString();
                    if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
                    {
                        if (double.TryParse(coldata, out d0))
                        {
                            d0 = Convert.ToDouble(coldata);
                        }
                        rowtemp.CreateCell(jj).SetCellValue(d0);
                    }
                    else
                    {
                        rowtemp.CreateCell(jj).SetCellValue(coldata);
                    }
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);

            return(ms);
        }
Example #8
0
        public string TQGZ_DAOCHU(string data)
        {
            string   token   = AppClass.GetSession("token").ToString();
            int      STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID"));
            DaoRuMsg msg     = new DaoRuMsg();

            try
            {
                WMS_JPD_JHD_TQGZ[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <WMS_JPD_JHD_TQGZ[]>(data);


                FileStream file       = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/过账授权导出模板.xlsx", FileMode.Open, FileAccess.Read);
                IWorkbook  workbook   = new XSSFWorkbook(file);
                ISheet     worksheet1 = workbook.GetSheet("过账授权");
                if (worksheet1 == null)
                {
                    msg.Msg  = "err";
                    msg.Info = "工作薄中没有工作表";
                    return(Newtonsoft.Json.JsonConvert.SerializeObject(msg));
                }

                //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4));
                int row1 = 1;
                for (int i = 0; i < model.Length; i++)
                {
                    int count = 0;
                    NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1);
                    row.CreateCell(count++).SetCellValue(model[i].JHD);
                    row.CreateCell(count++).SetCellValue(model[i].XSZZ);
                    row.CreateCell(count++).SetCellValue(model[i].XSZZNAME);
                    row.CreateCell(count++).SetCellValue(model[i].SDF);
                    row.CreateCell(count++).SetCellValue(model[i].SDFNAME);
                    row.CreateCell(count++).SetCellValue(model[i].KFZT == 1 ? "未开放" : "已开放");
                    row.CreateCell(count++).SetCellValue(model[i].CJTIME);
                    row.CreateCell(count++).SetCellValue(model[i].CJRNAME);
                    row.CreateCell(count++).SetCellValue(model[i].XGTIME);
                    row.CreateCell(count++).SetCellValue(model[i].XGRNAME);
                    row1++;
                }
                worksheet1.ForceFormulaRecalculation = true;
                string     now   = DateTime.Now.ToString("yyyy-MM");
                FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create);
                workbook.Write(file1);
                file1.Close();

                msg.Msg = now;
                return(Newtonsoft.Json.JsonConvert.SerializeObject(msg));
            }
            catch (Exception e)
            {
                msg.Msg  = "err";
                msg.Info = e.ToString();
                return(Newtonsoft.Json.JsonConvert.SerializeObject(msg));
            }
        }
Example #9
0
        private static void WriteData(ref NPOI.SS.UserModel.ISheet sheet1, DataTable dt, Dictionary <string, string> columnInfo, string datadesc, int datastartrow)
        {
            //int datastartrow = 0;
            if (!string.IsNullOrEmpty(datadesc))
            {
                NPOI.SS.UserModel.IRow desc = sheet1.CreateRow(datastartrow);
                for (int i = 0; i < 10; i++)
                {
                    desc.CreateCell(i);
                }
                NPOI.SS.Util.CellRangeAddress address = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9);
                sheet1.AddMergedRegion(address);
                sheet1.GetRow(0).GetCell(0).SetCellValue(datadesc);
                datastartrow++;
            }

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(datastartrow);
            int tick = 0;

            foreach (string value in columnInfo.Values)
            {
                row1.CreateCell(tick).SetCellValue(value);
                tick++;
            }

            //将数据逐步写入sheet1各个行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + datastartrow + 1);
                int index = 0;
                foreach (string key in columnInfo.Keys)
                {
                    bool isnumberic = false;
                    if (key.ToLower() == "rowid")
                    {
                        rowtemp.CreateCell(index).SetCellValue(i + 1);
                    }
                    else
                    {
                        string data = GetValue(dt.Rows[i], key, out isnumberic);
                        if (isnumberic && data != "--")
                        {
                            rowtemp.CreateCell(index).SetCellValue(string.IsNullOrEmpty(data) ? 0 : double.Parse(data));
                        }
                        else
                        {
                            rowtemp.CreateCell(index).SetCellValue(data);
                        }
                    }
                    index++;
                }
            }
        }
Example #10
0
        //导出 只传dt
        public Stream ExportExcel(DataTable dt)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);

            //组建表头
            DataColumnCollection dcc = dt.Columns;
            int index = 0;

            foreach (DataColumn i in dcc)
            {
                row1.CreateCell(index).SetCellValue(i.ColumnName);
                index = index + 1;
            }

            double d0 = 0;

            //将数据逐步写入sheet1各个行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);

                for (int jj = 0; jj < dcc.Count; jj++)
                {
                    string dbtype  = dcc[jj].DataType.ToString();
                    string coldata = dt.Rows[i][dcc[jj].ColumnName].ToString();
                    if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
                    {
                        if (double.TryParse(coldata, out d0))
                        {
                            d0 = Convert.ToDouble(coldata);
                        }
                        rowtemp.CreateCell(jj).SetCellValue(d0);
                    }
                    else
                    {
                        rowtemp.CreateCell(jj).SetCellValue(coldata);
                    }
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);

            return(ms);
        }
Example #11
0
        private void ExcelExport()
        {
            string sheet1Title = "Sheet1";

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(sheet1Title);

            NPOI.SS.UserModel.IRow headerRow4Sheet1 = sheet1.CreateRow(0);

            ExcelCellStyle cellStyle = new ExcelCellStyle(book);

            ICell cell;//初始化ICell准备设值

            #region 设定Sheet1的头行

            cell = headerRow4Sheet1.CreateCell(0); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户ID");
            cell = headerRow4Sheet1.CreateCell(1); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户名");
            cell = headerRow4Sheet1.CreateCell(2); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户地址");

            #endregion

            /*0行已经设为表头行,内容从第1行开始设值*/
            int sheet1RowID = 1;

            Customer[] listCustomer = client.GetCustomer("");

            foreach (Customer customer in listCustomer)
            {
                IRow r = sheet1.CreateRow(sheet1RowID);

                cell = r.CreateCell(0); cell.SetCellValue(customer.customerID); cell.CellStyle = cellStyle.style;
                cell = r.CreateCell(1); cell.SetCellValue(customer.customerName); cell.CellStyle = cellStyle.style;
                cell = r.CreateCell(2); cell.SetCellValue(customer.customerAddress); cell.CellStyle = cellStyle.style;

                sheet1RowID += 1;//每做一个循环就到下一行设置
            }

            /*以下是做自适应宽度*/
            ChangeStyle(book, sheet1);

            /*最后作输出流导出文件*/
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("客户列表信息", System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
Example #12
0
        public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
        {
            try
            {
                NPOI.SS.UserModel.IWorkbook book = null;
                if (excelExt == ExcelExt.Xls)
                {
                    book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                }
                else
                {
                    book = new NPOI.XSSF.UserModel.XSSFWorkbook();
                }

                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);

                // 添加表头
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                int index = 0;
                foreach (DataColumn item in dt.Columns)
                {
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(item.ColumnName);
                    index++;
                }

                // 添加数据
                int num = dt.Rows.Count;
                for (int i = 0; i < num; i++)
                {
                    index = 0;
                    row   = sheet.CreateRow(i + 1);
                    foreach (DataColumn item in dt.Columns)
                    {
                        NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        cell.SetCellValue(dt.Rows[i][item].ToString());
                        index++;
                    }
                }

                book.Write(outStream);
                book = null;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
Example #13
0
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="title"></param>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        public static void Export(string[] fileName, string[] title, string sheetName, DataTable dt)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
            NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
            for (int i = 0; i < title.Length; i++)
            {
                row.CreateCell(i).SetCellValue(title[i].ToString());
            }
            List <string> ids   = new List <string>();
            int           index = 0;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (ids.Contains(dt.Rows[i]["ID"].ToString()))
                {
                    continue;
                }
                else
                {
                    ids.Add(dt.Rows[i]["ID"].ToString());
                    row = sheet.CreateRow(index + 1);
                    index++;
                    for (int j = 0; j <= fileName.Length; j++)
                    {
                        if (j == 0)
                        {
                            row.CreateCell(j).SetCellValue(index + 1);
                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j - 1]].ToString());
                        }
                    }
                }
            }


            //写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + sheetName + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());

            book = null;
            ms.Close();
            ms.Dispose();
        }
Example #14
0
        public static void ExcelOut(DataTable dt, string fullXlsFilePath)
        {
            //创建Excel文件的对象
            NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1  = book.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow   headRow = sheet1.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                headRow.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
            }

            //将数据逐步写入sheet1各个行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //加上设置大小下载下来的.xlsx文件打开时才没有错误
            FileStream fileStream = new FileStream(fullXlsFilePath, FileMode.Create, FileAccess.Write);

            book.Write(fileStream);//调用这个后会关于文件流,在HSSFWorkbook不会关闭所以在处理时应注意
            FileStream fs       = new FileStream(fullXlsFilePath, FileMode.Open, FileAccess.Read);
            long       fileSize = fs.Length;

            //加上设置大小下载下来的.xlsx文件打开时才没有错误
            HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
            byte[] fileBuffer = new byte[fileSize]; fs.Read(fileBuffer, 0, (int)fileSize); HttpContext.Current.Response.BinaryWrite(fileBuffer); fs.Close();
        }
Example #15
0
        private static void CreatAddCell(NPOI.SS.UserModel.IRow dataRow, string value, NPOI.SS.UserModel.ICellStyle style, int col)
        {
            var cell = dataRow.CreateCell(col);

            cell.SetCellValue(value);
            cell.CellStyle = style;
        }
Example #16
0
        public void NpoiExcel(DataTable dt, string title)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book      = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet     = book.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow           headerrow = sheet.CreateRow(0);
            ICellStyle style = book.CreateCellStyle();

            style.Alignment         = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            for (int I = 0; I <= dt.Rows.Count - 1; I++)
            {
                HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    string DgvValue = dt.Rows[I][j].ToString();
                    row2.CreateCell(j).SetCellValue(DgvValue);
                    sheet.SetColumnWidth(j, 20 * 150);
                }
            }
            MemoryStream ms = new MemoryStream();

            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
Example #17
0
 public static MemoryStream ExportExcel(DataTable dt)
 {
     NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
     //添加一个sheet
     NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
     //给sheet1添加第一行的头部标题
     NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
     //row1.RowStyle.FillBackgroundColor = "";
     for (int i = 0; i < dt.Columns.Count; i++)
     {
         row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
     }
     //将数据逐步写入sheet1各个行
     for (int i = 0; i < dt.Rows.Count; i++)
     {
         NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
         for (int j = 0; j < dt.Columns.Count; j++)
         {
             rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
         }
     }
     // 写入到客户端
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     ms.Seek(0, SeekOrigin.Begin);
     return(ms);
 }
Example #18
0
        public void NpoiExcel(DataTable dt, string title, string s)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("Sheet1");

            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
            ICellStyle             style     = book.CreateCellStyle();

            style.Alignment         = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            string[] ss = s.Split(',');
            for (int i = 0; i < ss.Length; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(ss[i]);
            }
            for (int I = 0; I <= dt.Rows.Count - 1; I++)
            {
                HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    //double a = 0;
                    //string b = "";
                    //try
                    //{
                    //    a = Convert.ToDouble(dt.Rows[I][j].ToString());
                    //    row2.CreateCell(j).SetCellValue(a);
                    //    sheet.SetColumnWidth(j, 20 * 150);
                    //}
                    //catch
                    //{
                    //    b = dt.Rows[I][j].ToString();
                    //    row2.CreateCell(j).SetCellValue(b);
                    //    sheet.SetColumnWidth(j, 20 * 150);
                    //}
                    string DgvValue = dt.Rows[I][j].ToString();
                    if (j == 8 || j == 9)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue));
                    }
                    else
                    {
                        row2.CreateCell(j).SetCellValue(DgvValue);
                    }

                    sheet.SetColumnWidth(j, 20 * 150);
                }
            }
            MemoryStream ms = new MemoryStream();

            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
Example #19
0
        /// <summary>
        /// 批量导出需要导出的列表
        /// </summary>
        /// <returns></returns>
        public static MemoryStream ExportMemory(DataTable source, List <string> caption)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1");
            //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            for (int i = 0; i < caption.Count; i++)
            {
                var element = caption[i];
                row1.CreateCell(i).SetCellValue(element);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < source.Rows.Count; i++)
            {
                var dataRow = source.Rows[i];
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                for (int j = 0; j < caption.Count; j++)
                {
                    var keyValue = caption[j];
                    rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            DateTime dt       = DateTime.Now;
            string   dateTime = dt.ToString("yyMMddHHmmssfff");
            string   fileName = "查询结果" + dateTime + ".xls";

            return(ms);
            //return File(ms, "application/vnd.ms-excel", fileName);
        }
Example #20
0
 /// <summary>
 /// 服务商结算订单导出
 /// </summary>
 /// <param name="fileName"></param>
 /// <param name="title"></param>
 /// <param name="sheetName"></param>
 /// <param name="dt"></param>
 public static System.IO.MemoryStream ExcelSettlementOrder(string[] fileName, string[] title, string sheetName, DataTable dt)
 {
     NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
     NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
     NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
     for (int i = 0; i < title.Length; i++)
     {
         row.CreateCell(i).SetCellValue(title[i]);
     }
     if (dt != null && dt.Rows.Count > 0)
     {
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
             for (int j = 0; j < fileName.Length; j++)
             {
                 row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
             }
         }
     }
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     //ms.Seek(0, SeekOrigin.Begin);
     ms.Flush();
     ms.Position = 0;//流位置归零
     return(ms);
     //写入到客户端
 }
Example #21
0
 /// <summary>
 /// 批量导出需要导出的列表
 /// </summary>
 /// <returns></returns>
 public static MemoryStream GetMemoryStream(DataTable source, List <string> caption = null)
 {
     //创建Excel文件的对象
     NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
     NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1");
     //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了
     NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
     if (caption == null)
     {
         caption = source.Columns.Cast <DataColumn>().ToList().Select(item => item.Caption).ToList();
     }
     for (int i = 0; i < caption.Count; i++)
     {
         var element = caption[i];
         row1.CreateCell(i).SetCellValue(element);
     }
     //将数据逐步写入sheet1各个行
     for (int i = 0; i < source.Rows.Count; i++)
     {
         var dataRow = source.Rows[i];
         NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
         for (int j = 0; j < caption.Count; j++)
         {
             var keyValue = caption[j];
             rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
         }
     }
     // 写入到客户端
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     ms.Seek(0, SeekOrigin.Begin);
     return(ms);
     //return File(ms, "application/vnd.ms-excel", fileName);
 }
Example #22
0
        /// <summary>
        /// Excel导出返回文件路径
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <param name="dt"></param>
        /// <param name="FilePath"></param>
        /// <param name="ext"></param>
        /// <returns></returns>
        public static string ExcelFilePath(string[] fileName, string[] title, string sheetName, DataTable dt, string FilePath, string ext)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
            NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
            for (int i = 0; i < title.Length; i++)
            {
                row.CreateCell(i).SetCellValue(title[i]);
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < fileName.Length; j++)
                    {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
                    }
                }
            }

            if (!Directory.Exists(HttpContext.Current.Server.MapPath(FilePath)))
            {
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath(FilePath));
            }
            FilePath += "/" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000) + Path.GetExtension(ext);
            FileStream fs2 = File.Create(HttpContext.Current.Server.MapPath(FilePath));

            book.Write(fs2);
            fs2.Close();


            return(FilePath);
            //写入到客户端
        }
Example #23
0
        public void NpoiExcel(System.Data.DataTable dt, string file)
        {
            try
            {
                double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000;
                int    sheetCount    = (int)(Math.Ceiling(sheetCountdbl));


                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

                for (int c = 0; c < sheetCount; c++)
                {
                    string sheetname = "Sheet" + (c + 1).ToString();

                    NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);

                    NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
                    ICellStyle             style     = book.CreateCellStyle();
                    style.Alignment         = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = headerrow.CreateCell(i);
                        cell.CellStyle = style;
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                    }

                    int js = 0;//计数
                    //表内容
                    for (int i = c * 60000; i < dt.Rows.Count; i++)
                    {
                        if (js > 59999)
                        {
                            break;
                        }
                        IRow row = sheet.CreateRow(js + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row.CreateCell(j);
                            cell.CellStyle = style;
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }

                        js++;
                    }
                }


                FileStream fs = File.Create(file);
                book.Write(fs);
                fs.Flush();
                fs.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
Example #24
0
        /// <summary>
        /// 批量导出
        /// </summary>
        /// <returns></returns>
        public FileResult ExportStu()
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            int pager_totalcount            = (int)Session["pager_totalcount"];
            int totalCount = 0;
            //获取list数据
            List <ArticleEntity> infoList = new AchieveDAL.MyTestDAL().GetArticleList("", pager_totalcount, 1, out totalCount);

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            //创建时间	名称	商户订单号 | 交易号	对方	金额(元)	状态
            row1.CreateCell(0).SetCellValue("编号");
            row1.CreateCell(1).SetCellValue("标题");
            row1.CreateCell(2).SetCellValue("内容");
            int Width = 256;

            sheet1.SetColumnWidth(0, 10 * Width);
            sheet1.SetColumnWidth(1, 25 * Width);
            sheet1.SetColumnWidth(2, 60 * Width);
            if (infoList != null)
            {
                var list = infoList.OrderByDescending(p => p.ID);

                if (list != null)
                {
                    int i = 0;
                    //将数据逐步写入sheet1各个行
                    foreach (var item in list)
                    {
                        i = i + 1;
                        NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i);
                        rowtemp.CreateCell(0).SetCellValue(item.ID.ToString());
                        rowtemp.CreateCell(1).SetCellValue(item.Title == null ? "" : item.Title.ToString());
                        rowtemp.CreateCell(2).SetCellValue(item.Content == null ? "" : item.Content.ToString());
                    }
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", HttpUtility.UrlEncode("导出", Encoding.UTF8).ToString() + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
        }
Example #25
0
        public ActionResult Download(string downloadfile)
        {
            string[] titleList = new string[] { "顏色ID", "顏色", "三原色的 R", "三原色的 G", "三原色的 B" };
            var      list      = db.Colors.ToList();//獲取資料方法

            //建立工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();

            //建立sheet
            ISheet sheet1 = workbook.CreateSheet("sheet1");

            //給sheet1新增第一行的頭部標題
            IRow headerrow = sheet1.CreateRow(0);

            XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont      font      = (XSSFFont)workbook.CreateFont();

            font.FontHeightInPoints = 10;
            font.Boldweight         = 700;
            headStyle.SetFont(font);
            for (int i = 0; i < titleList.Length; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = headStyle;
                cell.SetCellValue(titleList[i]);
            }

            //將資料逐步寫入sheet1各個行
            for (int i = 0; i < list.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].ColorID);
                rowtemp.CreateCell(1).SetCellValue(Convert.ToString(list[i].ColorName));
                rowtemp.CreateCell(2).SetCellValue(Convert.ToInt32(list[i].R));
                rowtemp.CreateCell(3).SetCellValue(Convert.ToInt32(list[i].G));
                rowtemp.CreateCell(4).SetCellValue(Convert.ToInt32(list[i].B));
            }

            // 寫入到客戶端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();

            workbook.Write(ms);
            return(this.File(ms.ToArray(), "application/vnd.ms-excel", "顏色資料表.xlsx"));
        }
Example #26
0
        public IActionResult ExportExcel(string keyword = "")
        {
            DataAccess.TASM_CUSTOMERManager manager = new DataAccess.TASM_CUSTOMERManager();


            List <TASM_CUSTOMER> list = new List <TASM_CUSTOMER>();

            manager.ListByWhere(keyword, ref list);

            HSSFWorkbook excelBook = new HSSFWorkbook(); //创建工作簿Excel
            ISheet       sheet1    = excelBook.CreateSheet("项目履历表");

            IRow row1 = sheet1.CreateRow(0);

            row1.CreateCell(0).SetCellValue("客户名称");
            row1.CreateCell(1).SetCellValue("手机");

            row1.CreateCell(2).SetCellValue("邮箱");
            row1.CreateCell(3).SetCellValue("地址");
            row1.CreateCell(4).SetCellValue("创建时间");


            for (int i = 0; i < list.Count(); i++)
            {
                NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);

                rowTemp.CreateCell(0).SetCellValue(list[i].NAME);
                rowTemp.CreateCell(1).SetCellValue(list[i].PHONE);

                rowTemp.CreateCell(2).SetCellValue(list[i].EMIAL);
                rowTemp.CreateCell(3).SetCellValue(list[i].ADRESS);
                rowTemp.CreateCell(4).SetCellValue(list[i].CREATETIME.ToString());
            }

            var fileName = "客户信息表" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";//文件名

            //将Excel表格转化为流,输出

            MemoryStream bookStream = new MemoryStream();

            excelBook.Write(bookStream);
            bookStream.Seek(0, SeekOrigin.Begin);
            return(File(bookStream, "application/vnd.ms-excel", fileName));
        }
Example #27
0
        public ActionResult ExcelOut()
        {
            var users = _context.Users.ToList();

            ////创建工作簿Excel
            HSSFWorkbook book = new HSSFWorkbook();

            //为工作簿创建工作表并命名
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("用户信息");
            //创建第一行
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            //创建其他列并赋值(相当于表格的表头)
            row1.CreateCell(0).SetCellValue("姓名");
            row1.CreateCell(1).SetCellValue("性别");
            row1.CreateCell(2).SetCellValue("班级");
            row1.CreateCell(3).SetCellValue("地址");
            row1.CreateCell(4).SetCellValue("邮箱");
            //循环输出
            for (int i = 0; i < users.Count(); i++)
            {
                //创建行
                NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);
                rowTemp.CreateCell(0).SetCellValue(users[i].Name);
                if (users[i].Grader == "true")
                {
                    rowTemp.CreateCell(1).SetCellValue("男");
                }
                else
                {
                    rowTemp.CreateCell(1).SetCellValue("女");
                }
                rowTemp.CreateCell(2).SetCellValue(users[i].Phone);
                rowTemp.CreateCell(3).SetCellValue(users[i].Address);
                rowTemp.CreateCell(4).SetCellValue(users[i].Email);
            }
            //文件名
            var fileName = "用户信息报表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
            //将Excel表格转化为流,输出
            MemoryStream bookStream = new MemoryStream();                   //创建文件流

            book.Write(bookStream);                                         //文件写入流(向流中写入字节序列)
            bookStream.Seek(0, SeekOrigin.Begin);                           //输出之前调用Seek,把0位置指定为开始位置
            return(File(bookStream, "application/vnd.ms-excel", fileName)); //最后以文件形式返回
        }
Example #28
0
        /// <summary>
        /// Excel 导出
        /// Author:liuqijun 2015-4-13
        /// </summary>
        /// <param name="dt">数据源表格</param>
        /// <param name="sheetName">Excel sheetName</param>
        /// <param name="outStream">输出流</param>
        public static void Export(DataTable dt, string sheetName, Stream outStream)
        {
            try
            {
                if (dt == null)
                {
                    return;
                }

                HSSFWorkbook             workbook = new HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet;
                if (String.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.CreateSheet("sheet1");
                }
                else
                {
                    sheet = workbook.CreateSheet(sheetName);
                }

                //填充列名
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(0);
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }

                //填充内容
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dataRow = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
                //保存
                workbook.Write(outStream);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
Example #29
0
        public FileResult Export(Pagination rq, AdminCredential User)
        {
            #region 报表导出
            var report = service.Get(rq.vid).Data;
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ICellStyle style = workbook.CreateCellStyle();
            style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
            style.FillPattern         = FillPattern.SolidForeground;
            style.BorderTop           = BorderStyle.Thin;
            style.BorderLeft          = BorderStyle.Thin;
            style.BorderRight         = BorderStyle.Thin;
            style.BorderBottom        = BorderStyle.Thin;
            style.Alignment           = HorizontalAlignment.Center;
            style.VerticalAlignment   = VerticalAlignment.Center;

            IFont font = workbook.CreateFont();       //创建一个字体样式对象
            font.FontHeightInPoints = 10;             //字体大小
            font.FontName           = "宋体";           //和excel里面的字体对应
            font.Boldweight         = short.MaxValue; //字体加粗
            font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
            style.SetFont(font);                      //将字体样式赋给样式对象

            NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(report.Name);
            NPOI.SS.UserModel.IRow   row1   = sheet1.CreateRow(0);
            row1.Height = 430;

            var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(service.GetReportColumns(rq.vid.Value));
            int i         = 0;
            foreach (var item in FieldList)
            {
                ICell cell = row1.CreateCell(i);
                cell.SetCellValue(item.Title);
                cell.CellStyle = style;
                i++;
            }
            rq.Filter = Session[rq.vid.ToString() + "Filter"].ToString();
            DataTable dt = service.GetExportReportData(rq, User);
            //将数据逐步写入sheet1各个行
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                var row = dt.Rows[z];
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
                int c = 0;
                foreach (var item in FieldList)
                {
                    rowtemp.CreateCell(c++).SetCellValue(row[item.Field].ToString());
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", report.Name + ".xls"));

            #endregion
        }
Example #30
0
        /// <summary>
        /// Get a specific cell from a row. If the cell doesn't exist,
        /// </summary>
        /// <param name="row">The row that the cell is part of</param>
        /// <param name="column">The column index that the cell is in.</param>
        /// <returns>The cell indicated by the column.</returns>
        public static NPOI.SS.UserModel.ICell GetCell(NPOI.SS.UserModel.IRow row, int column)
        {
            NPOI.SS.UserModel.ICell cell = row.GetCell(column);

            if (cell == null)
            {
                cell = row.CreateCell(column);
            }
            return(cell);
        }