使用Aspose插件对Excel操作

使用使用Aspose插件对Excel文档进行导入导出操作

使用前请先下载Aspose插件引用

Excel导入：

前台使用file标签获取，submit方式提交。

<form id="form1" enctype="multipart/form-data" method="post" >

 <table class="table-condensed">

<tr>

                    <td class="text-right">导入表格：</td>

                    <td class="text-left"><input type="file" name="file1" class="btn btn-default btn-lg" /></td>

</tr>

<tr>

                    <td class="text-left">

                        <input type="submit" id="btnImport" name="btnImport" value="导入" class="btn btn-default" />

                    </td>

                </tr>

</table>

</form>

后台接收：

HttpPostedFileBase fileBase = Request.Files["file1"];//这里获取名称与前台标签name保持一致

if (fileBase != null)

            {

                string filename = Path.GetFileName(fileBase.FileName);

                string extension = Path.GetExtension(filename);

                string path = "/Upload/Test/" + DateTime.Now.ToString("yyyyMMdd") + "/";

                Directory.CreateDirectory(Path.GetDirectoryName(Request.MapPath(path)));

                string newFilename = DateTime.Now.ToString("yyyyMMddHHmmssfff");

                string fullFileName = path + newFilename + extension;

                fileBase.SaveAs(Request.MapPath(fullFileName));

　　　　　　　　　 try

                {

　　　　　　　　　　　 Stopwatch sw = new Stopwatch();//记录导入操作用时多长

                    sw.Start();

//这里可放入BLL方法处理

string result = new ProductBLL().ImportExcel(Request.MapPath(path), newFilename, extension);

//BLL方法 ProductBLL

public string ImportExcel(string path, string filename, string extension)
        {
            Workbook workbook = new Workbook(path + filename + extension);
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;
            for (int i = 1; i < cells.Rows.Count; i++)
            {
                try
                {
                    string brand = cells[i, 0].StringValue.Trim();//获取列值
                    string years = cells[i, 1].StringValue.Trim();
                }
                catch (Exception e)
                {
                    continue;
                }

            }
            return "OK";
        }

　　　　　　　　sw.Stop();

              long runTime = sw.ElapsedMilliseconds / ; //获取到操作用时多少秒

　　　　　　}

　　　　　　catch (Exception e)

                {

                    Log.Write("导入", "导入错误", "错误信息：" + e.Message);

                }

           }

Excel导出：

string path = "/Upload/Test/" + DateTime.Now.ToString("yyyyMMdd") + "/";

            Directory.CreateDirectory(Path.GetDirectoryName(Server.MapPath(path)));

            string newFilename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";

            string fullFileName = Server.MapPath(path + newFilename);

public void ExportInfo(List<Test> list, string fullFileName)

        {

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();

            Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[];

            cellSheet.PageSetup.LeftMargin = 0.3;//左边距

            cellSheet.PageSetup.RightMargin = 0.3;//右边距

            cellSheet.PageSetup.TopMargin = ;//上边距

            cellSheet.PageSetup.BottomMargin = 0.5;//下边距

            cellSheet.PageSetup.FooterMargin = 0.5;//页脚

            cellSheet.PageSetup.HeaderMargin = 0.5;//页眉

            cellSheet.PageSetup.Orientation = PageOrientationType.Landscape;

            cellSheet.PageSetup.CenterHorizontally = true;//水平居中

            cellSheet.PageSetup.CenterVertically = true;

cellSheet.Cells[, ].PutValue("货号");

            cellSheet.Cells[, ].PutValue("颜色");

            cellSheet.Cells[, ].PutValue("尺码");

int i = ;

            foreach (var item in list)

            {

cellSheet.Cells[i, ].PutValue(item.productno);

                cellSheet.Cells[i, ].PutValue(item.size);

                cellSheet.Cells[i, ].PutValue(item.color);

i++;

            }

cellSheet.AutoFitColumns();

            fullFileName = Path.GetFullPath(fullFileName);

            workbook.Save(fullFileName);

}

return File(fullFileName, "application/ms-excel", UserName + "_Test单" + newFilename);// 方法Action里直接返回File文件下载。

//DataTable数据源导出，封装ToExcel方法。
da.ToExcel(fullFileName);
return File(fullFileName, "application/ms-excel", UserName + "_Test单" + newFilename);

　　　　 /// <summary>
        /// DataTable数据表保存至Excel
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="fullFileName">文件完整路径</param>
        public static void ToExcel(this System.Data.DataTable dt, string fullFileName)
        {

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];

cellSheet.Name = dt.TableName;

int rowIndex = 0;
            int colIndex = 0;
            int colCount = dt.Columns.Count;
            int rowCount = dt.Rows.Count;

//列名的处理
            for (int i = 0; i < colCount; i++)
            {
                cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
                //cellSheet.Cells[rowIndex, colIndex].SetStyle.Font.IsBold = true;
                //cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
                colIndex++;
            }

Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
            style.Font.Name = "Arial";
            style.Font.Size = 10;
            Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
            cellSheet.Cells.ApplyStyle(style, styleFlag);

rowIndex++;

for (int i = 0; i < rowCount; i++)
            {
                colIndex = 0;
                for (int j = 0; j < colCount; j++)
                {
                    cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
                    colIndex++;
                }
                rowIndex++;
            }
            cellSheet.AutoFitColumns();

fullFileName = Path.GetFullPath(fullFileName);
            workbook.Save(fullFileName);
        }

 由于抽取部分代码出来，排版和引用方面未做详细注释，前台和后台方法对应即可。

做个笔记日后方便自己查看使用。做个笔记日后方便自己查看使用。做个笔记日后方便自己查看使用。做个笔记日后方便自己查看使用。