读取Excel文件并写入asp.net中的相同文件 [英] Read excel file and write in to same file in asp.net

问题描述

我想阅读Ms-excel模板并写入相同的excel文件
我该怎么办，请帮助我...

简单地说
我想将数据附加到现有的Excel文件中，该如何实现????


在此先感谢

Hi,

I want to read an Ms-excel template and write in to the same excel file
how can i do that please help me...

in simple words
I want to append data in existing excel file how do i achieve that???


thanks in advance

推荐答案

您好，Indrajeet，
您可以使用Idll.dll之类的第三方dll写入Excel Sheet.
您可以使用Workbook，WritableWorkbook，WritableSheet类对象来写入Excel文件.确保您已经有一个模板Excel文件；通过文件流读取它并也使用上面的类.
随附的示例代码可从数据库读取值并将其写入Excel文件.

Hi Indrajeet,
You can use third party dlls like IExcel.dll for writing into Excel Sheet.
You can use Workbook,WritableWorkbook,WritableSheet class objects for writing into Excel file.Make sure you have a existing Template Excel file;read it through a file stream and use the above classes also.
Enclosed is the sample code that reads values from Database and writes to Excel file.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using IExcel;
using AjaxControlToolkit;
using IExcel.write;
using IExcel.read;
using System.IO;
using IExcel.biff;


public enum ExcelColumnNames
    {
        A = 0, B = 1, C = 2, D = 3, E = 4, F = 5, G = 6, H = 7, I = 8,
        S = 18, T = 19, U = 20, V = 21, W = 22, X = 23,
        Y = 24, Z = 25, AA = 26, AB = 27, AC = 28
    }
    public partial class ExportToExcel : System.Web.UI.Page
    {
        BlClass blobj = new BlClass();
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        FileInfo fi = null;
        WritableSheet XlSheetSummary = null;
        //WritableSheet temp = null;
        protected void btnExportToExcel_Click(object sender, EventArgs e)
        {
            ArrayList excelal = blobj.BllGetAndroidMetrics();
            fi = new FileInfo(Server.MapPath("Templates/" + "DesignTemplate.xls"));
            Workbook wb = Workbook.getWorkbook(fi);
            string FileName = Server.MapPath("Resources/" + "ExportToExcel" + ".xls");
            WritableWorkbook wrib = Workbook.createWorkbook(new FileInfo(FileName), wb);
            //to rename a sheet
            Sheet sh = wb.getSheet(0);
            wrib.importSheet("Hello", 0, sh);
            
            //To Write Data to a cell (row/column) in a Sheet
            XlSheetSummary = wrib.getSheet(1);//XlSheetSummary is a WritableSheet object.
            int intRow = 4;
            
            for (int i = 0; i < excelal.Count; i++)
            {
                setExcelCellValue(XlSheetSummary, Convert.ToInt32(ExcelColumnNames.B), intRow, ((ModelMetrics)(excelal[i])).Platform1);
                setExcelCellValue(XlSheetSummary, Convert.ToInt32(ExcelColumnNames.C), intRow, (Convert.ToInt32(((ModelMetrics)(excelal[i])).ApiLevel1)).ToString());
                setExcelCellValue(XlSheetSummary, Convert.ToInt32(ExcelColumnNames.D), intRow, (((ModelMetrics)(excelal[i])).Distribution1).ToString());
                intRow++;
            }
            
            XlSheetSummary.setName("Android Data");
            //To Add Image to excel start
                 FileInfo fiimg = new FileInfo(Server.MapPath("/Reports/chartReport.PNG"));
                 setExcelImageValue(XlSheetSummary, fiimg);
            //Write inage end
            wrib.removeSheet(0);
            wrib.removeSheet(2);
            wrib.removeSheet(3);
            wrib.write();
            wrib.close();
            //Convert File stream to bytes data
            System.IO.FileStream fs = new System.IO.FileStream(FileName, System.IO.FileMode.Open);
            Byte[] b = new byte[fs.Length];
            fs.Read(b, 0, (int)fs.Length);
            fs.Close();
            //Delete file from Savelocation
            try
            {
                FileInfo TheFile = new FileInfo(FileName);
                if (TheFile.Exists)
                {
                    File.Delete(FileName);
                }
                else
                {
                    throw new FileNotFoundException();
                }
            }
            catch (FileNotFoundException)
            {
                ApplicationLog.WriteInfo(ex.Message, "ExportExcel.btnExportToExcel()", Session["UserId"].ToString());
            }
            //Send output stream to client window
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment; filename=AndroidMetrics.xls");
            Response.BinaryWrite(b);
            Response.Flush();
            Response.End();
        }
        //To write Data to Cell
        public void setExcelCellValue(WritableSheet xlSheet, int intCol, int intRow, string strCellText)
        {
            // For Writing the Text.    
            IExcel.write.Label lblCellValue;
            lblCellValue = new IExcel.write.Label(intCol, intRow, strCellText);
            xlSheet.addCell(lblCellValue);
        }
        //To Write Image to Excel Cell
        public void setExcelImageValue(WritableSheet xlSheet,FileInfo image)
        {
            IExcel.write.WritableImage imgCellValue;
            imgCellValue = new IExcel.write.WritableImage(Convert.ToDouble(4), Convert.ToDouble(ExcelColumnNames.F),7.5, 14.5, image);
            xlSheet.addImage(imgCellValue);
        }
        protected void btnShowData_Click(object sender, EventArgs e)
        {
            ArrayList al = new ArrayList();
            gvValues.Visible = true;
            btnExportToExcel.Visible = true;
            al = blobj.BllGetAndroidMetrics();
            gvValues.DataSource = al;
            gvValues.DataBind();
        }
    }

希望这对您有帮助...

Hope this helps...

检查此处 [ ^ ]

这里是读者

Here is a reader

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Threading;
using System.IO;
namespace Excel
{
    public class Reader
    {
        public static DataTable XLSREADER(string fileName, string firstcell, string lastcell)
        {
            try
            {
                //Example
                //DataTable Tablez = XLSREADER("Example.xls","A1","D5")
                //DataRow rowz = Tablez.Rows[row#];
                //Double num = Convert.ToDouble(row[element#]);               
                string ConnectionString = Provider.SetConn(fileName);
                OleDbConnection objConn = new OleDbConnection(ConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1

这篇关于读取Excel文件并写入asp.net中的相同文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！