Beispiel #1
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++;
                }
            }
        }
Beispiel #2
0
 public static void SetCellValue(NPOI.SS.UserModel.ICell iCell, object cellValue)
 {
     if (iCell == null)
     {
         return;
     }
     if (cellValue != null)
     {
         if (cellValue.GetType() == typeof(DBNull))
         {
             iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
         }
         else if (cellValue.GetType() == typeof(bool))
         {
             iCell.SetCellValue((bool)cellValue);
         }
         else if (cellValue.GetType() == typeof(string))
         {
             if (cellValue.ToString() == "#NUM!")
             {
                 iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
             }
             else
             {
                 iCell.SetCellValue((string)cellValue);
             }
         }
         else if (cellValue.GetType() == typeof(DateTime))
         {
             NPOI.SS.UserModel.ICellStyle  iCellStyle  = iCell.Sheet.Workbook.CreateCellStyle();
             NPOI.SS.UserModel.IDataFormat iDataFormat = iCell.Sheet.Workbook.CreateDataFormat();
             iCellStyle.DataFormat = iDataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
             iCell.CellStyle       = iCellStyle;
             iCell.SetCellValue((DateTime)cellValue);
         }
         else if (cellValue.GetType() == typeof(int) || cellValue.GetType() == typeof(double) || cellValue.GetType() == typeof(float))
         {
             iCell.SetCellValue((double)cellValue);
         }
         else
         {
             iCell.SetCellValue(cellValue.ToString());
         }
     }
 }
Beispiel #3
0
        public void Export(Stream ms, DataTable dt, string postfix)
        {
            //var fileName = m_Handler.GetFileName();
            NPOI.SS.UserModel.IWorkbook book = null;
            if (postfix == ".xls")
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else if (postfix == ".xlsx")
            {
                book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }
            else
            {
                throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名");
            }
            m_Handler.Encode(ref dt);

            //WriteExcel(ref book, 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++;
                }
            }
            //// 写入
            //MemoryStream ms = new MemoryStream();
            book.Write(ms);
            book = null;

            //using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write))
            //{
            //    byte[] data = ms.ToArray();
            //    fs.Write(data, 0, data.Length);
            //    fs.Flush();
            //}

            //ms.Close();
            //ms.Dispose();
        }
Beispiel #4
0
        private void explort()
        {
            SaveFileDialog sflg = new SaveFileDialog();

            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }
            //this.gridView1.ExportToXls(sflg.FileName);
            //NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.IWorkbook book = null;
            if (sflg.FilterIndex == 1)
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else
            {
                book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }

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

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

            foreach (DataGridViewColumn item in this.dataGridView2.Columns)
            {
                if (item.Visible)
                {
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(item.HeaderText);
                    index++;
                }
            }

            // 添加数据

            for (int i = 0; i < this.dataGridView2.Rows.Count; i++)
            {
                index = 0;
                row   = sheet.CreateRow(i + 1);
                foreach (DataGridViewColumn item in this.dataGridView2.Columns)
                {
                    if (item.Visible)
                    {
                        if (dataGridView2.Rows[i].Cells[item.HeaderText].Value != null)
                        {
                            NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cell.SetCellValue(dataGridView2.Rows[i].Cells[item.HeaderText].Value.ToString().Trim());
                        }
                        index++;
                    }
                }
            }
            // 写入
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            book = null;

            using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }

            ms.Close();
            ms.Dispose();
        }
Beispiel #5
0
        private void sb_save_Click(object sender, EventArgs e)
        {
            SaveFileDialog sflg = new SaveFileDialog();

            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }
            int aa = 0;

            NPOI.SS.UserModel.IWorkbook book = null;
            if (sflg.FilterIndex == 1)
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else
            {
                book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }
            foreach (XtraTabPage trp in this.xtraTabControl1.TabPages)
            {
                XtraTabPage xinka = trp;
                try
                {
                    this.active_nepCalaTable = xinka.Controls[0] as NepCalaTable;
                }
                catch (Exception err)
                {
                    MessageBox.Show("出现了错误: " + err.Message);
                }
                aa += 1;

                //this.gridView1.ExportToXls(sflg.FileName);
                //NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                //NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(string.Format("test_{0}", aa));
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(xinka.Text);
                // 添加表头
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                int      index             = 0;
                GridView ac    = this.active_nepCalaTable.GC;
                int      count = ac.Columns.Count;
                for (int i = 0; i < count; i++)
                {
                    string Caption = ac.Columns[i].Caption;
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(Caption);
                    index++;
                }

                // 添加数据

                DataTable dt = this.active_nepCalaTable.DDT;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    index = 0;
                    row   = sheet.CreateRow(j + 1);
                    for (int k = 0; k < count; k++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        //cell.SetCellValue(this.gridView1.GetRowCellValue(i, item).ToString());
                        cell.SetCellValue(dt.Rows[j][k].ToString());
                        index++;
                    }
                }
                NPOI.SS.UserModel.IRow r1 = sheet.GetRow(0);
                // 第一排
                NPOI.SS.UserModel.ICell cell1 = r1.CreateCell(12);
                cell1.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell1.SetCellValue("激发点距");
                NPOI.SS.UserModel.ICell cell11 = r1.CreateCell(13);
                cell11.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell11.SetCellValue(this.active_nepCalaTable.jifa_dianju.Text);

                // 第二排
                r1 = sheet.GetRow(1);
                NPOI.SS.UserModel.ICell cell2 = r1.CreateCell(12);
                cell2.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell2.SetCellValue("接收点距");
                NPOI.SS.UserModel.ICell cell21 = r1.CreateCell(13);
                cell21.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell21.SetCellValue(this.active_nepCalaTable.jieshou_dianju.Text);



                // 第三排
                r1 = sheet.GetRow(2);
                NPOI.SS.UserModel.ICell cell3 = r1.CreateCell(12);
                cell3.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell3.SetCellValue("接收点数");
                NPOI.SS.UserModel.ICell cell31 = r1.CreateCell(13);
                cell31.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell31.SetCellValue(this.active_nepCalaTable.jieshou_dianshu.Text);



                // 第四排
                r1 = sheet.GetRow(3);
                NPOI.SS.UserModel.ICell cell4 = r1.CreateCell(12);
                cell4.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell4.SetCellValue("激发线距");
                NPOI.SS.UserModel.ICell cell41 = r1.CreateCell(13);
                cell41.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell41.SetCellValue(this.active_nepCalaTable.jifa_xianju.Text);



                // 第五排
                r1 = sheet.GetRow(4);
                NPOI.SS.UserModel.ICell cell5 = r1.CreateCell(12);
                cell5.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell5.SetCellValue("接受线距");
                NPOI.SS.UserModel.ICell cell51 = r1.CreateCell(13);
                cell51.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell51.SetCellValue(this.active_nepCalaTable.jieshou_xianju.Text);

                // 第六排
                r1 = sheet.GetRow(5);
                NPOI.SS.UserModel.ICell cell6 = r1.CreateCell(12);
                cell6.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell6.SetCellValue("纵向滚动距离");
                NPOI.SS.UserModel.ICell cell61 = r1.CreateCell(13);
                cell61.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell61.SetCellValue(this.active_nepCalaTable.muban_zong.Text);

                // 第7排
                r1 = sheet.GetRow(6);
                NPOI.SS.UserModel.ICell cell7 = r1.CreateCell(12);
                cell7.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell7.SetCellValue("模板炮");
                NPOI.SS.UserModel.ICell cell71 = r1.CreateCell(13);
                cell71.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell71.SetCellValue(this.active_nepCalaTable.mobanpao.Text);


                // 第8排
                r1 = sheet.GetRow(7);
                NPOI.SS.UserModel.ICell cell8 = r1.CreateCell(12);
                cell8.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell8.SetCellValue("接收线数");
                NPOI.SS.UserModel.ICell cell81 = r1.CreateCell(13);
                cell81.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell81.SetCellValue(this.active_nepCalaTable.jieshou_xianshu.Text);



                // 第9排
                r1 = sheet.GetRow(8);
                NPOI.SS.UserModel.ICell cell9 = r1.CreateCell(12);
                cell9.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell9.SetCellValue("横向滚动距离");
                NPOI.SS.UserModel.ICell cell91 = r1.CreateCell(13);
                cell91.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell91.SetCellValue(this.active_nepCalaTable.muban_heng.Text);



                // 第10排
                r1 = sheet.GetRow(9);
                NPOI.SS.UserModel.ICell cell10 = r1.CreateCell(12);
                cell10.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell10.SetCellValue("布设接受线数");
                NPOI.SS.UserModel.ICell cell101 = r1.CreateCell(13);
                cell101.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell101.SetCellValue(this.active_nepCalaTable.bushe_jieshouxianshu.Text);



                // 第11排
                r1 = sheet.GetRow(10);
                NPOI.SS.UserModel.ICell cell1_1 = r1.CreateCell(12);
                cell1_1.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell1_1.SetCellValue("布设激发线数");
                NPOI.SS.UserModel.ICell cell111 = r1.CreateCell(13);
                cell111.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell111.SetCellValue(this.active_nepCalaTable.bushe_jifaxianshu.Text);


                // 写入
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                book.Write(ms);
                //book = null;

                using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }

                ms.Close();
                ms.Dispose();
            }
            book = null;
            MessageBox.Show("保存成功了");
        }
        public void DownlodExcel()
        {
            SaveFileDialog sflg = new SaveFileDialog();

            sflg.Filter   = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            sflg.FileName = "采购单细节导入模板表.xls";
            if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }

            NPOI.SS.UserModel.IWorkbook book = null;
            if (sflg.FilterIndex == 1)
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("采购细节表");

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

            NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
            cell.SetCellValue("品名");

            #region 医疗器械(这里不用)
            //if ((int)this.toolStripComboBox1.ComboBox.SelectedValue == (int)PurchaseDrugTypes.医疗器械)
            //{
            //    cell = row.CreateCell(1);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("型号");

            //    cell = row.CreateCell(2);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("规格");

            //    cell = row.CreateCell(3);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("单位");

            //    cell = row.CreateCell(4);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("生产厂家");

            //    cell = row.CreateCell(5);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
            //    cell.SetCellValue("数量");

            //    cell = row.CreateCell(6);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
            //    cell.SetCellValue("单价");

            //    cell = row.CreateCell(7);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
            //    cell.SetCellValue("税率(%)");
            //}
            //else
            #endregion
            {
                cell = row.CreateCell(1);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("剂型");

                cell = row.CreateCell(2);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("规格");

                cell = row.CreateCell(3);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("单位");

                cell = row.CreateCell(4);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("生产厂家");

                cell = row.CreateCell(5);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("产地");

                cell = row.CreateCell(6);
                cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                cell.SetCellValue("数量");

                cell = row.CreateCell(7);
                cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                cell.SetCellValue("单价");

                cell = row.CreateCell(8);
                cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                cell.SetCellValue("税率(%)");
            }

            // 写入
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            book = null;

            using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }

            ms.Close();
            ms.Dispose();
        }
Beispiel #7
0
        private async void executeExportCommand(object obj)
        {
            IsRunning = true;
            await Task.Run(() =>
            {
                try
                {
                    reader.CheckPermission();

                    var filePath = Path.Combine(reader.GetFolderPath(), "SmsExport-" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xlsx");
                    var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
                    var sheet    = workbook.CreateSheet("Sheet1");
                    //第一行
                    {
                        var row        = sheet.CreateRow(0);
                        var cColumnInd = 0;

                        row.CreateCell(cColumnInd).SetCellValue("编号");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("号码");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("时间");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("类型");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("内容");
                        cColumnInd++;
                    }
                    //当前行号
                    var cRowInd = 1;

                    var totalCount = reader.GetCount(StartIndex);
                    var enumerator = reader.GetEnumerator(StartIndex);
                    var index      = 0;
                    while (enumerator.MoveNext())
                    {
                        index++;
                        var item    = enumerator.Current;
                        var typeStr = "";
                        switch (item.ItemType)
                        {
                        case SmsItem.SmsItemType.Send:
                            typeStr = "发送";
                            break;

                        case SmsItem.SmsItemType.Recv:
                            typeStr = "接收";
                            break;

                        default:
                            continue;
                        }
                        {
                            var cColumnInd = 0;

                            var row = sheet.CreateRow(cRowInd);
                            NPOI.SS.UserModel.ICell cell = null;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Id);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Address);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Date.ToString("yyyy-MM-dd HH:mm:ss"));
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(typeStr);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Body);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;
                        }
                        cRowInd++;
                        Progress = index * 1D / totalCount;
                        Message  = $"读取短信[{index} / {totalCount}]中...";
                    }
                    Message = $"正在保存到文件...";
                    using (var stream = File.OpenWrite(filePath))
                        workbook.Write(stream);
                    Message = $"已保存到{filePath}";
                }
                catch (Exception ex)
                {
                    Message = "错误," + ex;
                }
            });

            IsRunning = false;
        }
Beispiel #8
0
        /// <summary>
        /// 将excel文件内容读取到DataTable数据表中
        /// </summary>
        /// <param name="fileName">文件完整路径名</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名:true=是,false=否</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
        {
            //定义要返回的datatable对象
            DataTable data = new DataTable();

            //excel工作表
            NPOI.SS.UserModel.ISheet sheet = null;
            //数据开始行(排除标题行)
            int startRow = 0;

            try
            {
                if (!File.Exists(fileName))
                {
                    return(null);
                }
                //根据指定路径读取文件
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                //根据文件流创建excel数据结构
                NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
                //IWorkbook workbook = new HSSFWorkbook(fs);
                //如果有指定工作表名称
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    //如果没有指定的sheetName,则尝试获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
                    //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.LastCellNum;
                    //如果第一行是标题列名
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        NPOI.SS.UserModel.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)
            {
                throw ex;
            }
        }
        public String ExportProductsList([FromUri] String id = "")
        {
            String url     = Request.RequestUri.ToString();
            String fileUrl = "";

            List <Product> productsList = new List <Product>();

            if (id.Trim().Length == 0)
            {
                productsList = getFullList();
                fileUrl      = url.Replace("api/ExportProductsList/", "templates/");
                url          = url.Replace("api/ExportProductsList/", "Content/imgs/");
            }
            else
            {
                productsList = getFilteredList(id);
                fileUrl      = url.Replace("api/ExportProductsList/" + id, "templates/");
                url          = url.Replace("api/ExportProductsList/" + id, "Content/imgs/");
            }



            String filename         = DateTime.Now.ToString("yyyyMMddHHmmss") + "_products.xls";
            String filepath         = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/templates"), filename);
            String filepathtemplate = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/templates"), "template.xls");

            fileUrl = fileUrl + filename;

            var fs = new FileStream(filepathtemplate, FileMode.Open, FileAccess.Read);
            var templateWorkbook = new HSSFWorkbook(fs, true);

            NPOI.SS.UserModel.ISheet sheet = templateWorkbook.GetSheet("ProductsList");

            int i = 1;

            foreach (Product product in productsList)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.GetRow(i);

                NPOI.SS.UserModel.ICell cell0 = dataRow.GetCell(0);
                cell0.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell0.SetCellValue(product.Id);

                NPOI.SS.UserModel.ICell cell1 = dataRow.GetCell(1);
                cell1.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell1.SetCellValue(product.Code);


                NPOI.SS.UserModel.ICell cell2 = dataRow.GetCell(2);
                cell2.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell2.SetCellValue(product.Name);

                NPOI.SS.UserModel.ICell cell3 = dataRow.GetCell(3);
                cell3.SetCellType(NPOI.SS.UserModel.CellType.String);
                var tempURL = "";
                if (product.Photo.Trim().Length > 0)
                {
                    tempURL = url + product.Photo;
                }
                cell3.SetCellValue(tempURL);

                NPOI.SS.UserModel.ICell cell4 = dataRow.GetCell(4);
                cell4.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                cell4.SetCellValue(product.Price);

                NPOI.SS.UserModel.ICell cell5 = dataRow.GetCell(5);
                cell5.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell5.SetCellValue(product.LastUpdate.ToString("yyyyMMdd HH:mm:ss"));

                i += 1;
            }


            try
            {
                File.Delete(filepath);
            }
            catch { }
            var memoryStream = new MemoryStream();

            templateWorkbook.Write(memoryStream);
            Byte[] content = memoryStream.ToArray();
            System.IO.File.WriteAllBytes(filepath, content);


            return(fileUrl);
        }
        //
        #region 根据批次和书号导出excel
        public void ExportExcel(int batchNum, long bookid)
        {
            //
            int    rowNum       = 0;
            string saveFileName = batchNum.ToString() + "_" + bookid.ToString();
            string xlsSheetName = "";

            ///
            using (chefuzi_dataEntities myOperating = new chefuzi_dataEntities())//数据库操作
            {
                #region 要开通那本图书
                Child_Book_Study myChild_Book_Study = new Child_Book_Study();
                myChild_Book_Study = myOperating.Child_Book_Study.FirstOrDefault(p => p.BookID == bookid);
                if (myChild_Book_Study != null)
                {
                    xlsSheetName = myChild_Book_Study.BookName;
                    saveFileName = myChild_Book_Study.BookName;
                }
                #endregion
                //
                if (!String.IsNullOrWhiteSpace(saveFileName))
                {
                    NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
                    NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(xlsSheetName);
                    // 添加数据
                    IQueryable <Sys_BookStudy_QRCode> myDataTable = myOperating.Sys_BookStudy_QRCode.Where(p => p.BatchNum == batchNum && p.BookStudyID == bookid);
                    NPOI.SS.UserModel.IRow            row         = null;
                    foreach (Sys_BookStudy_QRCode item in myDataTable)
                    {
                        row = sheet.CreateRow(rowNum);
                        NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        cell.SetCellValue(StaticVarClass.QRCodeUrl + "1/" + item.QRCode);
                        sheet.SetColumnWidth(0, 20000);
                        rowNum++;
                    }
                    #region 导出文件
                    if (rowNum > 0)
                    {
                        // 写入
                        System.IO.MemoryStream ms = new System.IO.MemoryStream();
                        book.Write(ms);
                        book = null;
                        //
                        int updateCount = myOperating.Sys_BookStudy_QRCode.Where(p => p.BatchNum == batchNum && p.BookStudyID == bookid && p.PrintStatus == false).Update(q => new Sys_BookStudy_QRCode()
                        {
                            PrintStatus = true
                        });
                        if (updateCount > 0)
                        {
                            EFCachClear.ClearTable("Sys_BookStudy_QRCode");
                        }
                        //
                        Response.ClearHeaders();
                        Response.Clear();
                        Response.Expires = 0;
                        Response.Buffer  = true;
                        Response.AddHeader("Accept-Language", "zh-tw");
                        //
                        saveFileName = saveFileName + "_" + batchNum.ToString() + "_" + rowNum + ".xls";
                        //
                        Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName, System.Text.Encoding.UTF8));
                        Response.ContentType = "application/octet-stream;charset=gbk";
                        Response.BinaryWrite(ms.ToArray());
                        ms.Close();
                        ms.Dispose();
                        Response.End();
                    }
                    else
                    {
                        Response.AddHeader("Accept-Language", "zh-tw");
                        Response.Write("没有任何数据导出,请选择图书和批次。");
                        Response.End();
                    }
                    #endregion
                }
            }
        }