ASP.NET NPOI Excel导入、导出，高级、全面、总结版

最新推荐文章于 2024-02-04 16:51:12 发布

ChinaLzw

最新推荐文章于 2024-02-04 16:51:12 发布

阅读量9.8k

点赞数 1

分类专栏：删除多余行 excel asp.net web npoi ASP.NET实战日记文章标签：导入导出 excel NPOI 复杂表头

本文为博主原创文章，只为分享而编，欢迎各位学习以及转载。

本文链接：https://blog.csdn.net/qq285679784/article/details/45078615

版权

asp.net web 同时被 3 个专栏收录

169 篇文章 1 订阅

订阅专栏

ASP.NET实战日记

21 篇文章 2 订阅

订阅专栏

excel

6 篇文章 0 订阅

订阅专栏

如果有不懂，欢迎随时提问，在评论处留下你的疑问、建议、意见。本主会及时处理。哈哈哈哈。。。

导语：客户需求中Excel导入、导出处理几乎都是需要的。以下导入、导出的代码处理，是根据不同的用户提出的需求，总结出来的。让我们看看都有哪些：
1、导出后，需要导入(毋容置疑，导入的必须是正式的Excel)
2、导出的Excel，需要各种标题要求，单行合并、多行合并，表格样式、标题字体、字体大小等等。
3、导出的Excel，多种用途，需要用于导入修改数据库数据、用于统计(数值统计表格内容必须是数字格式)
4、复杂表头表Excel导入怎么处理？答：将Excel上传到服务器，打开服务器Excel，删除多余的表头，只留下对应数据库表的那一行。这样就变成单行表头的Excel了。
5、。。。。情况太多，就不一一列举了。

进入正题。。。

正式Excel导出，设置单元格字体、边框、上下左右居中、数值防止科学计数法（先将表头写入一个Excel，设置好表头格式，保存到项目文件夹，作为导出的模(mu)板。用模板可以省去写表头的代码，并且可以防止导入的时候因代码创建的表头出现异常）。导出数据源是DataTable，如果是其它的做下代码调整即可。一起来看代码吧

引用到的命名空间，下载NPOI.dll并引用到项目中
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;

private void ToExcel(DataSet ds)
{
string filePath = “”;
bool check = false;
try
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
string filename = “KCPD_” + Session[“UserID”].ToString() + “_” + DateTime.Now.ToString(“yyyyMMddhhmmss”) + “.xls”;
//临时存放路径
filePath = Server.MapPath(“~/upload/” + filename);
//Excel模版
string masterPath = Server.MapPath(“~/upload/Master/KCPD.xls”);
//复制Excel模版
File.Copy(masterPath, filePath);

                #region  将文件的只读勾取消 以免发生不必要的错误
                // 先把文件的属性读取出来 
                FileAttributes attrs = File.GetAttributes(filePath);

                // 下面表达式中的 1 是 FileAttributes.ReadOnly 的值 
                // 此表达式是把 ReadOnly 所在的位改成 0, 
                attrs = (FileAttributes)((int)attrs & ~(1));

                File.SetAttributes(filePath, attrs);
                #endregion

                FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                Workbook hssfworkbook = new HSSFWorkbook(file);
                Sheet sheet = hssfworkbook.GetSheet("Sheet1");

                #region 设置单元格样式
                NPOI.SS.UserModel.Font dataFont = hssfworkbook.CreateFont();
                dataFont.FontHeightInPoints = 9;
                //dataFont.Boldweight = 10;
                dataFont.FontName = "宋体";

                CellStyle celldatastyle = hssfworkbook.CreateCellStyle();
                //celldatastyle.Alignment = HorizontalAlignment.CENTER_SELECTION;
                celldatastyle.VerticalAlignment = VerticalAlignment.CENTER;
                celldatastyle.BorderBottom = CellBorderType.THIN;
                celldatastyle.BorderLeft = CellBorderType.THIN;
                celldatastyle.BorderRight = CellBorderType.THIN;
                celldatastyle.BorderTop = CellBorderType.THIN;
                celldatastyle.WrapText = true;
                celldatastyle.Indention = 0;//缩进
                celldatastyle.SetFont(dataFont);
                #endregion

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //这里需要注意顺序
                    //[配件ID],[识别码],[配件名称],[配件规格],[配件代码],[版本],[上月库存],[采购入库],[良品入库],[维修返回],
                    //[维修入库],[调整库存],[调拨入库],[销售出库],[免费出库],[调拨出库],[维修出库],[其它出库],[现有库存],[盘点库存]

                    Row row = sheet.CreateRow(i + 1);
                    row.CreateCell(0).SetCellValue(Convert.ToInt32(dt.Rows[i]["配件ID"].ToString()));
                    row.CreateCell(1).SetCellValue(dt.Rows[i]["识别码"].ToString());
                    row.CreateCell(2).SetCellValue(dt.Rows[i]["配件名称"].ToString());
                    row.CreateCell(3).SetCellValue(dt.Rows[i]["配件规格"].ToString());
                    row.CreateCell(4).SetCellValue(dt.Rows[i]["配件代码"].ToString());
                    row.CreateCell(5).SetCellValue(dt.Rows[i]["版本"].ToString());
                    row.CreateCell(6).SetCellValue(Convert.ToInt32(dt.Rows[i]["上月库存"].ToString()));
                    row.CreateCell(7).SetCellValue(Convert.ToInt32(dt.Rows[i]["采购入库"].ToString()));
                    row.CreateCell(8).SetCellValue(Convert.ToInt32(dt.Rows[i]["良品入库"].ToString()));
                    row.CreateCell(9).SetCellValue(Convert.ToInt32(dt.Rows[i]["维修返回"].ToString()));

                    row.CreateCell(10).SetCellValue(Convert.ToInt32(dt.Rows[i]["维修入库"].ToString()));
                    row.CreateCell(11).SetCellValue(Convert.ToInt32(dt.Rows[i]["调整库存"].ToString()));
                    row.CreateCell(12).SetCellValue(Convert.ToInt32(dt.Rows[i]["调拨入库"].ToString()));
                    row.CreateCell(13).SetCellValue(Convert.ToInt32(dt.Rows[i]["销售出库"].ToString()));
                    row.CreateCell(14).SetCellValue(Convert.ToInt32(dt.Rows[i]["免费出库"].ToString()));
                    row.CreateCell(15).SetCellValue(Convert.ToInt32(dt.Rows[i]["调拨出库"].ToString()));
                    row.CreateCell(16).SetCellValue(Convert.ToInt32(dt.Rows[i]["维修出库"].ToString()));
                    row.CreateCell(17).SetCellValue(Convert.ToInt32(dt.Rows[i]["其它出库"].ToString()));
                    row.CreateCell(18).SetCellValue(Convert.ToInt32(dt.Rows[i]["现有库存"].ToString()));
                    row.CreateCell(19).SetCellValue(Convert.ToInt32(dt.Rows[i]["盘点库存"].ToString()));

                    #region 设置单元格样式
                    row.GetCell(0).CellStyle = celldatastyle;
                    row.GetCell(1).CellStyle = celldatastyle;
                    row.GetCell(2).CellStyle = celldatastyle;
                    row.GetCell(3).CellStyle = celldatastyle;
                    row.GetCell(4).CellStyle = celldatastyle;
                    row.GetCell(5).CellStyle = celldatastyle;
                    row.GetCell(6).CellStyle = celldatastyle;
                    row.GetCell(7).CellStyle = celldatastyle;
                    row.GetCell(8).CellStyle = celldatastyle;
                    row.GetCell(9).CellStyle = celldatastyle;

                    row.GetCell(10).CellStyle = celldatastyle;
                    row.GetCell(11).CellStyle = celldatastyle;
                    row.GetCell(12).CellStyle = celldatastyle;
                    row.GetCell(13).CellStyle = celldatastyle;
                    row.GetCell(14).CellStyle = celldatastyle;
                    row.GetCell(15).CellStyle = celldatastyle;
                    row.GetCell(16).CellStyle = celldatastyle;
                    row.GetCell(17).CellStyle = celldatastyle;
                    row.GetCell(18).CellStyle = celldatastyle;
                    row.GetCell(19).CellStyle = celldatastyle;
                    #endregion
                }

                using (FileStream filess = File.OpenWrite(filePath))
                {
                    hssfworkbook.Write(filess);//讲Sheet1写入Excel
                }

                check = true;
                file.Close();//关闭FileStream对象

                // 输出副本的二进制字节流
                HttpContext.Current.Response.Charset = "UTF-8"; // 或UTF-7 以防乱码
                HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.GetEncoding(65001).GetBytes(filename)));
                Response.BinaryWrite(File.ReadAllBytes(filePath));
            }

        }
        catch (Exception ex)
        {
            excel_msg.Text = "导入返回信息：导出失败！错误信息：" + ex.Message;
            this.excel_msg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            if (check)
            {
                //删除副本
                File.Delete(filePath);
            }
        }
    }

正式Excel导入，导入的数据千奇百怪，最重要的是复杂表头的导入。遇到复杂表头的时候，可以用代码先把多余的表头行删除，只留下对应数据库表的那一行即可。代码适用于大量数据的导入，而且处理速度杠杠的，对导入数据进行全面扫描，扫描出所有不符合指定格式的数据，并指出出现问题的行和列，以Excel方式将错误返回前端，提供用户下载

处理步骤：导入Excel——复制副本——删除多余表头——创建临时表——调用批量导入控件将数据复制到临时表——调用存储过程扫描错误并处理——将处理结果插入到Excel并返回到客户端提供客户下载

引用到的命名空间，下载NPOI.dll并引用到项目中
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;

—————————————————-

—-判断是否是整数，如果不是整数,返回String。
—-User:Lzw

—-Time:2015-04-05 11:09

CREATE FUNCTION [dbo].[IsNumber](@StrVal nvarchar(max))
RETURNS nvarchar(10)
AS
BEGIN

DECLARE @Flag nvarchar(10) 

if IsNumeric(@StrVal)=1
begin
    SET @Flag='Number'
end
else
begin
    SET @Flag='String'
end

RETURN @Flag

END

用于扫描Excel的存储过程
– exec SP_Check_Import_T_Fittings_Collect_tmp ‘KCPD_c0f5459f_0c87_4144_b222_0624c3c0414a’,1000,1,’2015-03-01’,false
/*
User：Lzw
Date：2015年4月4日 10:08:29
Name：盘点数据导入处理(扫描数据,返回异常详细信息)
*/
CREATE PROC [dbo].[SP_Check_Import_T_Fittings_Collect_tmp]
@Nname nvarchar(100), –创建的临时表名称
@UserID varchar(100),
@DepotID varchar(100),
@CollectTime varchar(100),
@IsImport bit
as

/*要点说明：
([ID]+1)[Row] –行数,注意：(ID + Excel中表头占据的行数)=行号
全面扫描数据，返回异常信息]存储过程

Tips：Excel数据中只要存在异常，则导入失败，将异常详细信息返回客户端
*/

DECLARE @SQL nvarchar(max),@SQL1 nvarchar(max),@SQL2 nvarchar(max),@SQL3 nvarchar(max)

SET @SQL=’
SELECT f_id,COUNT()Num INTO #Repeat FROM [‘+@Nname+’] GROUP BY f_id HAVING COUNT()>1
DECLARE @RepeatNum INT
SELECT @RepeatNum=COUNT(*) FROM #Repeat

SELECT f_id,[盘点库存] into #topKC FROM [T_Fittings_Collect] WHERE DepotId=’+@DepotID+’ AND CollectTime =DateAdd(m,-1,”’+@CollectTime+”’)

–现有库存!=盘点库存总数
DECLARE @Num INT
SELECT ([ID]+1) Row into #now_old FROM [‘+@Nname+’] where [现有库存]<>[盘点库存]
SELECT @Num=COUNT(*) FROM [‘+@Nname+’] where [现有库存]<>[盘点库存]

–检测数据是否存在异常
select ([ID]+1)[Row] –对应Excel Sheet1表中的行号
,[f_id]
,[sn]
,[FittingName]
,[SpecName]
,[FittingId]
,[FittingVersion]
,[LastAmount]
,[采购入库]
,[良品入库]
,[维修返回]
,[维修入库]
,[调整库存]
,[调拨入库]
,[销售发货]
,[免费发货]
,[办事处发货]
,[维修]
,[其它]
,[现有库存]
,[盘点库存]
,”’+@CollectTime+”’ as [CollectTime]
,’+@DepotID+’ as [DepotId]
,’+@UserID+’ as [UserId]
,(case when [f_id] is null or [f_id] =”” or [dbo].IsNumber=”String” then ”配件ID[ ”+isnull([f_id],”NULL”)+” ]无效! ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 上月库存 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 采购入库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 良品入库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 维修返回 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 维修入库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 调整库存 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 调拨入库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 销售出库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 免费出库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 调拨出库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 维修出库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 其它出库 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 现有库存 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end)+
(case when [dbo].IsNumber=”String” then ”[ 盘点库存 ]列格式不正确!请检查是否包含不合法字符 ; ” else ”” end) Error
into #Base
from [‘+@Nname+’]
order by [ID] desc

’
SET @SQL1=’

–检测数据是否已存在
SELECT DISTINCT b.[f_id] IsExist,a.* into #Data from #Base a
left join
T_Fittings_Collect_tmp b on a.CollectTime=b.CollectTime and a.[DepotId] =b.[DepotId] and a.[f_id]=b.[f_id]

–统计( 上月[盘点库存] != 本月[期初库存] )记录
DECLARE @noTop INT

SELECT
a.[Row],a.f_id,[LastAmount],isnull(b.[盘点库存],0)[盘点库存] –上月[盘点库存] 和本月[期初库存]
into #noTop
FROM #Base a
LEFT JOIN

topKC b ON a.f_id=b.f_id

SELECT @noTop=COUNT(*) FROM #noTop where [LastAmount]<>[盘点库存]

–异常总计
declare @Error_Rowber int
select @Error_Rowber=count(*) from #Base where Error<>””

–影响行数
declare @InsertRow int –插入总记录数
set @InsertRow=0
declare @UpdateRow int –修改总记录数
set @UpdateRow=0

–数据不存在异常
if @Error_Rowber=0 AND @noTop=0 AND (@Num=0 OR ‘+convert(varchar(10),@IsImport)+’=1)
begin

--Insert部分
INSERT INTO [dbo].[T_Fittings_Collect_tmp](
   [f_id],[sn],[FittingName],[SpecName],[FittingId],[FittingVersion]
  ,[LastAmount],[采购入库],[良品入库],[维修返回],[维修入库]
  ,[调整库存],[调拨入库],[销售发货],[免费发货],[办事处发货]
  ,[维修],[其它],[现有库存],[盘点库存]
  ,[CollectTime],[DepotId],[UserId]
)
select 
   [f_id],[sn],[FittingName],[SpecName],[FittingId],[FittingVersion]
  ,[LastAmount],[采购入库],[良品入库],[维修返回],[维修入库]
  ,[调整库存],[调拨入库],[销售发货],[免费发货],[办事处发货]
  ,[维修],[其它],[现有库存],[盘点库存]
  ,[CollectTime],[DepotId],[UserId]
from #Data where IsExist is null
set @InsertRow=@@ROWCOUNT

’
SET @SQL2=’
–Update部分
update [T_Fittings_Collect_tmp]
set [LastAmount]=convert([decimal](10, 0),b.[LastAmount]),
[采购入库]=convert([decimal](10, 0),b.[采购入库]),
[良品入库]=convert([decimal](10, 0),b.[良品入库]),
[维修返回]=convert([decimal](10, 0),b.[维修返回]),
[维修入库]=convert([decimal](10, 0),b.[维修入库]),
[调整库存]=convert([decimal](10, 0),b.[调整库存]),
[调拨入库]=convert([decimal](10, 0),b.[调拨入库]),
[销售发货]=convert([decimal](10, 0),b.[销售发货]),
[免费发货]=convert([decimal](10, 0),b.[免费发货]),
[办事处发货]=convert([decimal](10, 0),b.[办事处发货]),
[维修]=convert([decimal](10, 0),b.[维修]),
[其它]=convert([decimal](10, 0),b.[其它]),
[现有库存]=convert([decimal](10, 0),b.[现有库存]),
[盘点库存]=convert([decimal](10, 0),b.[盘点库存]),
UserID=’+@UserID+’
from [T_Fittings_Collect_tmp] a,#Data b
where a.CollectTime=b.CollectTime
and a.[DepotId] =b.[DepotId]
and a.[f_id]=b.[f_id]
and b.IsExist is not null
set @UpdateRow=@@ROWCOUNT

SELECT @InsertRow as InsertRow,@UpdateRow as UpdateRow

--删除审核信息
delete T_Fittings_Collect_date where depotid='+@DepotID+' and datediff(m,CollectDate,'''+@CollectTime+''')=0
--重新添加审核信息
insert into T_Fittings_Collect_date(depotid,collectdate,CreateUserid) values('+@DepotID+','''+@CollectTime+''','+@UserID+')

end
–数据存在异常，把异常详细信息反馈到客户端
else
begin

SELECT @InsertRow as InsertRow,@UpdateRow as UpdateRow
IF @noTop<>0
BEGIN
    SELECT identity(int,1,1)序号,* into #error_top FROM (
    SELECT (''第[ ''+convert(nvarchar(100),([Row]))+'' ]行'')Excel行号,(Error)异常详细信息 from #Data where Error<>''''
    UNION ALL
    SELECT ''第[ ''+convert(nvarchar(100),([Row]))+'' ]行'',''上月[盘点库存(''+convert(nvarchar(100),[LastAmount])+'')]、本月[期初库存(''+convert(nvarchar(100),[盘点库存])+'')]不一致，无法导入。'' FROM #noTop where [LastAmount]<>[盘点库存]
    )DATA

    select * from #error_top
    drop table #error_top
END

’
SET @SQL3=’
ELSE IF (@Num>0 and ‘+convert(varchar(10),@IsImport)+’=0)
BEGIN
SELECT identity(int,1,1)序号,* into #error FROM (
SELECT (”第[ ”+convert(nvarchar(100),([Row]))+” ]行”)Excel行号,(Error)异常详细信息 from #Data where Error<>””
UNION ALL
SELECT ”第[ ”+convert(nvarchar(100),([Row]))+” ]行”,”[ 现有库存 ]、[ 盘点库存 ]不一致，需要拥有[导入特殊权限]方可导入。” FROM #now_old
)DATA

    select * from #error
    drop table #error
END
ELSE IF @RepeatNum=0
BEGIN
    SELECT ROW_NUMBER() OVER(ORDER BY [Row] asc)序号,(''第[ ''+convert(nvarchar(100),([Row]))+'' ]行'')Excel行号,(Error)异常详细信息 from #Data where Error<>''''
END
ELSE
BEGIN

    SELECT ROW_NUMBER() OVER(ORDER BY [Row] asc)序号,(''第[ ''+convert(nvarchar(100),([Row]))+'' ]行'')Excel行号,(Error)异常详细信息 from #Data where Error<>''''
    UNION ALL
    SELECT 100000,''第[ n ]行'',''[ 配件ID ]列存在重复数据：''+[f_id]+'' , 重复次数：''+convert(nvarchar(100),Num) FROM #Repeat
END

end

drop table #Base,#Data,#Repeat,#topKC,#noTop,#now_old

PRINT @SQL
PRINT @SQL1
PRINT @SQL2
PRINT @SQL3
EXEC(@SQL+@SQL1+@SQL2+@SQL3)

region 导入Excel

    /// <summary>
    /// 立即导入Excel
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnUp_Click(object sender, System.EventArgs e)
    {
        if (this.drop_DepotId_In.SelectedItem.Value == "-1")
        { excel_msg.Text = "导入返回信息：仓库不能为空！"; this.Linkbtn.Visible = false; this.excel_msg.ForeColor = System.Drawing.Color.Red; return; }

        if (this.fileExcel.PostedFile.FileName.Length == 0)
        { excel_msg.Text = "导入返回信息：没有选择文件！"; this.Linkbtn.Visible = false; this.excel_msg.ForeColor = System.Drawing.Color.Red; return; }

        string ext = this.fileExcel.PostedFile.FileName.Substring(fileExcel.PostedFile.FileName.LastIndexOf('.') + 1).ToLower();
        if (ext != "xls")
        { excel_msg.Text = "导入返回信息：只能导入xls格式的Excel文件！"; this.Linkbtn.Visible = false; this.excel_msg.ForeColor = System.Drawing.Color.Red; return; }

        //记录导入耗时
        DateTime time = DateTime.Now;
        US.Components.UploadFileHelper upfile = new US.Components.UploadFileHelper();
        upfile.UserId = Session["UserID"].ToString();
        upfile.Upload_Type = 0;

        upfile.Save(this.fileExcel.PostedFile);

        //临时表名
        string Nname = ("KCPD_" + Guid.NewGuid().ToString()).Replace("-", "_");

        //创建唯一的临时表
        string CreateSql = string.Format(@"
                CREATE TABLE [dbo].[{0}]
                (
                    [ID] [int] IDENTITY(1,1) NOT NULL,
                    [f_id] [varchar](200),
                    [sn] [varchar](200),
                    [FittingName] [varchar](200),
                    [SpecName] [varchar](200),
                    [FittingId] [varchar](100),
                    [FittingVersion] [varchar](50),
                    [LastAmount] [varchar](200),
                    [采购入库] [varchar](200),
                    [良品入库] [varchar](200),
                    [维修返回] [varchar](200),
                    [维修入库] [varchar](200),
                    [调整库存] [varchar](200),
                    [调拨入库] [varchar](200),
                    [销售发货] [varchar](200),
                    [免费发货] [varchar](200),
                    [办事处发货] [varchar](200),
                    [维修] [varchar](200),
                    [其它] [varchar](200),
                    [现有库存] [varchar](200),
                    [盘点库存] [varchar](200),
                    [CollectTime] [datetime] ,
                    [DepotId] [int],
                    [UserId] [int]
                )", Nname);

        run.ExecSQL(CreateSql);

        try
        {
            string connectionString = ConfigurationSettings.AppSettings["ConnectionString"].Replace("True", "False");
            string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + upfile.FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

            string sqlstr = "select * from [Sheet1$]";

            #region OleDb导入
            using (OleDbConnection sourceConnection = new OleDbConnection(constr))
            {
                sourceConnection.Open();

                OleDbCommand ocomm = new OleDbCommand(sqlstr, sourceConnection);
                OleDbDataReader reader = ocomm.ExecuteReader();

                using (SqlConnection destinationConnection = new SqlConnection(connectionString))
                {
                    destinationConnection.Open();

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
                    {
                        bulkCopy.BatchSize = 1000;//每次传输的行数
                        bulkCopy.BulkCopyTimeout = 3600;

                        //f_id,sn,FittingName,SpecName,FittingId,FittingVersion,LastAmount,采购入库,良品入库,维修返回,
                        //维修入库,调整库存,调拨入库,销售发货,免费发货,办事处发货,维修,其它,现有库存,盘点库存,
                        //CollectTime,DepotId,UserId
                        //第一个参数为Excel中的字段   
                        //第二个参数为数据库表中的字段

                        bulkCopy.ColumnMappings.Add("配件ID", "f_id");
                        bulkCopy.ColumnMappings.Add("识别码", "sn");
                        bulkCopy.ColumnMappings.Add("配件名称", "FittingName");
                        bulkCopy.ColumnMappings.Add("配件规格", "SpecName");
                        bulkCopy.ColumnMappings.Add("配件代码", "FittingId");

                        bulkCopy.ColumnMappings.Add("版本", "FittingVersion");
                        bulkCopy.ColumnMappings.Add("上月库存", "LastAmount");
                        bulkCopy.ColumnMappings.Add("采购入库", "采购入库");
                        bulkCopy.ColumnMappings.Add("良品入库", "良品入库");
                        bulkCopy.ColumnMappings.Add("维修返回", "维修返回");

                        bulkCopy.ColumnMappings.Add("维修入库", "维修入库");
                        bulkCopy.ColumnMappings.Add("调整库存", "调整库存");
                        bulkCopy.ColumnMappings.Add("调拨入库", "调拨入库");
                        bulkCopy.ColumnMappings.Add("销售出库", "销售发货");
                        bulkCopy.ColumnMappings.Add("免费出库", "免费发货");

                        bulkCopy.ColumnMappings.Add("调拨出库", "办事处发货");
                        bulkCopy.ColumnMappings.Add("维修出库", "维修");
                        bulkCopy.ColumnMappings.Add("其它出库", "其它");
                        bulkCopy.ColumnMappings.Add("现有库存", "现有库存");
                        bulkCopy.ColumnMappings.Add("盘点库存", "盘点库存");

                        //tbl_cell_gh_temp为目标表   
                        bulkCopy.DestinationTableName = Nname;

                        //将数据复制到数据库
                        bulkCopy.WriteToServer(reader);

                        //关闭reader
                        reader.Close();

                        /*对导入的数据，进行全面扫描。
                        如果有异常则导入失败，将异常信息以Excel的方式返回客户端，提供下载
                        (返回的InsertRow=0，UpdateRow=0则为异常)*/
                        //获取影响行数
                        DataSet ds = run.GetDataSet(
                            string.Format(@"
                                exec SP_Check_Import_T_Fittings_Collect_tmp '{0}',{1},{2},'{3}',{4}", Nname, Session["UserID"].ToString(), drop_DepotId_In.SelectedValue, this.txt_inTime.Text + "-01", this.txtIsImport.Value)
                        );//@Nname、@UserID、@DepotID、@CollectTime、@IsImport

                        //Insert总数
                        string InsertRow = ds.Tables[0].Rows[0]["InsertRow"].ToString();
                        string UpdateRow = ds.Tables[0].Rows[0]["UpdateRow"].ToString();

                        //无异常，导入成功提示
                        if (InsertRow != "0" || UpdateRow != "0")
                        {
                            //隐藏下载Excel按钮
                            Linkbtn.Visible = false;

                            //Tips
                            excel_msg.Text = "<br/><br/>导入成功，总耗时：" + (DateTime.Now - time).ToString() + "<br/><br/>共插入数据:" + InsertRow + "条，修改数据：" + UpdateRow + "条";
                            excel_msg.ForeColor = System.Drawing.Color.Blue;
                        }
                        else
                        {
                            //Tips
                            excel_msg.Text = "<br/><br/>导入失败，数据存在异常，查看异常详细信息，请下载:";
                            excel_msg.ForeColor = System.Drawing.Color.Blue;

                            //将异常数据缓存起来
                            Cache.Insert("Error", ds);
                            //显示下载Excel按钮
                            Linkbtn.Visible = true;
                        }

                    }
                }
            }
            #endregion

        }
        catch (Exception ex)
        {
            excel_msg.Text = "导入返回信息：导入失败！错误信息：" + ex.Message;
            this.excel_msg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            if (upfile.FilePath != null)
            {
                delfile(upfile.FilePath);//删除已上传文件
            }
            //删除临时表
            string sql = "drop table  [dbo].[" + Nname + "]";
            run.ExecSQL(sql);
        }

    }

    /// <summary>
    /// 删除上传的临时文件
    /// </summary>
    /// <param name="filestr"></param>
    private void delfile(string filestr)
    {
        FileInfo fi = new FileInfo(filestr);

        if (fi.Exists)
        {
            fi.Delete();
        }
    }

    /// <summary>
    /// 下载异常信息
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Linkbtn_Click(object sender, EventArgs e)
    {
        if (Cache["Error"] != null)
        {
            DataSet ds = (DataSet)Cache["Error"];

            //数据存在异常，将异常详细信息导入Excel放到输出流，提供用户下载浏览
            CreateExcel(ds, "1", "系统扫描结果.xls");
        }
        else
        {
            excel_msg.Text = "无异常信息！";
            this.excel_msg.ForeColor = System.Drawing.Color.Red;
            //隐藏下载Excel按钮
            Linkbtn.Visible = false;
        }
    }

    /// <summary>
    /// 将DataSet中的数据导出到Excel
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="typeid"></param>
    /// <param name="FileName"></param>
    public void CreateExcel(DataSet ds, string typeid, string FileName)
    {
        HttpResponse resp;
        resp = Page.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ";");
        //resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
        string colHeaders = "", ls_item = "";
        int i = 0;

        //定义表对象和行对像，同时用DataSet对其值进行初始化 
        DataTable dt = ds.Tables[1];
        DataRow[] myRow = dt.Select("");
        // typeid=="1"时导出为EXCEL格式文档；typeid=="2"时导出为XML格式文档 
        if (typeid == "1")
        {
            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < dt.Columns.Count; i++)
            {
                if (i == dt.Columns.Count - 1)
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }
            }
            //向HTTP输出流中写入取得的数据信息 
            resp.Write(colHeaders);
            //逐行处理数据 
            foreach (DataRow row in myRow)
            {
                //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                for (i = 0; i < dt.Columns.Count; i++)
                {

                    if (i == dt.Columns.Count - 1)
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                resp.Write(ls_item);
                ls_item = "";
            }
        }
        else
        {
            if (typeid == "2")
            {
                //从DataSet中直接导出XML数据并且写到HTTP输出流中 
                resp.Write(ds.GetXml());
            }
        }
        //写缓冲区中的数据到HTTP头文档中
        resp.End();
    }
    #endregion

ChinaLzw

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
9
评论
ASP.NET NPOI Excel导入、导出，高级、全面、总结版

正式Excel导入，导入的数据千奇百怪，最重要的是复杂表头的导入。遇到复杂表头的时候，可以用代码先把多余的表头行删除，只留下对应数据库表的那一行即可。代码适用于大量数据的导入，而且处理速度杠杠的，对导入数据进行全面扫描，扫描出所有不符合指定格式的数据，并指出出现问题的行和列，以Excel方式将错误返回前端，提供用户下载处理步骤：导入Excel——复制副本——删除多余表头——创建临时表——调用批量导入控件将数据复制
复制链接

扫一扫