美高梅注册:及样式设置,对excel的使用总结

美高梅注册:及样式设置,对excel的使用总结。using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Core;
using Aspose.Cells;
using System.IO;
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.ComponentModel;
美高梅注册:及样式设置,对excel的使用总结。using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Windows.Forms;

解决思路:

美高梅注册:及样式设置,对excel的使用总结。使用应用Aspose插件对Excel文档进行导入导出操作

namespace WindowsFormsApplication1
美高梅注册:及样式设置,对excel的使用总结。{
    public partial class Form1 : Form
    {
        public Form1()
        {
美高梅注册:及样式设置,对excel的使用总结。            InitializeComponent();
        }

壹 、找个可用的Aspose.Cells(有钱还是买个正版吧,哪个人开发个东西也不易于);

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

        /// <summary>
        /// 生成EXCEL
        /// </summary>
        /// <param name=”sender”></param>
        /// <param name=”e”></param>
        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();

贰 、在.Net方案中援引此Cells;

Excel导入:

        
            dt.Columns.Add(“Product”, Type.GetType(“System.String”));
            dt.Columns.Add(“Version”, Type.GetType(“System.String”));
            dt.Columns.Add(“Description”,
Type.GetType(“System.String”));

叁 、写个函数ToExcel(传递3个DataTable),能够另写个SetCellStyle的函数专门给各列设置样式。

 

            DataRow newRow;
            newRow = dt.NewRow();
            newRow[“Product”] = “大话西游”;
            newRow[“Version”] = “2.0”;
            newRow[“Description”] = “作者很喜欢”;
            dt.Rows.Add(newRow);
            newRow = dt.NewRow();
            newRow[“Product”] = “大话西游”;
            newRow[“Version”] = “2.0”;
            newRow[“Description”] = “笔者很喜欢”;
            dt.Rows.Add(newRow);
            newRow = dt.NewRow();
            newRow[“Product”] = “大话西游”;
            newRow[“Version”] = “4.0”;
            newRow[“Description”] = “我很欢快”;
            dt.Rows.Add(newRow);

肆 、在按钮的Click事件中调用此ToExcel()即可。

前台使用file标签获取,submit形式交给。

            newRow = dt.NewRow();
            newRow[“Product”] = “梦幻西游”;
            newRow[“Version”] = “3.0”;
            newRow[“Description”] = “比大话更孩子气”;
            dt.Rows.Add(newRow);
            newRow = dt.NewRow();
            newRow[“Product”] = “梦幻西游”;
            newRow[“Version”] = “3.1”;
            newRow[“Description”] = “比大话更孩子气”;
            dt.Rows.Add(newRow);
            newRow = dt.NewRow();
            newRow[“Product”] = “梦幻西游”;
            newRow[“Version”] = “3.1”;
            newRow[“Description”] = “比大话更孩子气”;
            dt.Rows.Add(newRow);

注:想更详尽摸底的到Aspose网站去,那里有广大在线文书档案,够你看的了。也可以下载个德姆o程序研商。

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

            newRow = dt.NewRow();
            newRow[“Product”] = “坤灵网游”;
            newRow[“Version”] = “1.1”;
            newRow[“Description”] = “比大话更孩子气”;
            dt.Rows.Add(newRow);
            newRow = dt.NewRow();
            newRow[“Product”] = “坤灵网游”;
            newRow[“Version”] = “1.1”;
            newRow[“Description”] = “比大话更孩子气”;
            dt.Rows.Add(newRow);
            newRow = dt.NewRow();
            newRow[“Product”] = “坤灵网游”;
            newRow[“Version”] = “1.1”;
            newRow[“Description”] = “比大话更孩子气”;
            dt.Rows.Add(newRow);

 

后台接受:

            string path =
System.IO.Path.Combine(Application.StartupPath, “test01.xls”);
            if (File.Exists(path))
            {
                File.Delete(path);
            }

一部分代码贴在此间,代码还有要求改进的地点,一时半刻这么用着,给本身以往方便寻找,再者给真正接触此控件的同志们抛个砖头:

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 / 1000; //获取到操作用时多少秒
      }
      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[0];

            cellSheet.PageSetup.LeftMargin = 0.3;//左边距
            cellSheet.PageSetup.RightMargin = 0.3;//右边距
            cellSheet.PageSetup.TopMargin = 1;//上边距
            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[0, 0].PutValue("货号");
            cellSheet.Cells[0, 1].PutValue("颜色");
            cellSheet.Cells[0, 2].PutValue("尺码");

int i = 1;
            foreach (var item in list)
            {
cellSheet.Cells[i, 0].PutValue(item.productno);
                cellSheet.Cells[i, 1].PutValue(item.size);
                cellSheet.Cells[i, 2].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);

 
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];

ExportToExcel()的:

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

相关文章