Excel导入导出数据库02-阿里云开发者社区

开发者社区> james8888> 正文

Excel导入导出数据库02

简介: excel导入时还要保存字体、其背景颜色等信息时读取方法就要改变: 1 using System; 2 using System.Collections.Generic; 3 using System.
+关注继续查看

excel导入时还要保存字体、其背景颜色等信息时读取方法就要改变:

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.IO;
  6 using System.Data.OleDb;
  7 using System.Data;
  8 using Microsoft.Office.Interop.Excel;
  9 using System.Reflection;
 10 using System.Runtime.InteropServices;
 11 
 12 namespace WinOrderAd
 13 {
 14     public class Excel
 15     {
 16         public string FilePath
 17         {
 18             get;
 19             set;
 20         }
 21         public Dictionary<string, string> FiledNames
 22         {
 23             get;
 24             set;
 25         }
 26         public Excel()
 27         {
 28         }
 29 
 30         public DataSet ImportExcel()//若只需要知道数据就用此方法
 31         {
 32 
 33             try
 34             {
 35                 string strConn;
 36                 if (Path.GetExtension(FilePath) == ".xlsx")
 37                     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
 38                 else
 39                     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
 40                 OleDbConnection OleConn = new OleDbConnection(strConn);
 41                 OleConn.Open();
 42                 System.Data.DataTable table = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
 43                 DataSet OleDsExcle = new DataSet();
 44                 for (int i = 0; i < table.Rows.Count; i++)
 45                 {
 46                     string tableName = table.Rows[i]["Table_Name"].ToString();
 47                     tableName = tableName.Replace("'", "");
 48                     if (tableName.EndsWith("$"))
 49                     {
 50                         string sql = "SELECT * FROM [" + tableName + "]";
 51                         OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
 52                         OleDaExcel.Fill(OleDsExcle, tableName);
 53                         OleConn.Close();
 54                     }
 55                 }
 56                 return OleDsExcle;
 57             }
 58             catch (Exception err)
 59             {
 60                 throw err;
 61             }
 62         }
 63 
 64 
 65         /// <summary>
 66         /// 用Excel Com组件方式读取Excel内容到DataSet(兼容性较高)
 67         /// </summary>
 68         /// <param name="path"></param>
 69         /// <returns></returns>
 70         public DataSet ToDataTableEx()
 71         {
 72             Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
 73 
 74             excel.Visible = false;
 75             excel.ScreenUpdating = false;
 76             excel.DisplayAlerts = false;
 77 
 78             excel.Workbooks.Add(FilePath);
 79             Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
 80             Range rangecell = null;
 81             DataSet ds = new DataSet();
 82             try
 83             {
 84                 //遍历Worksheets中的每张表
 85                 for (int i = 1; i <= excel.Worksheets.Count; i++)
 86                 {
 87                     //获得指定表
 88                     worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[i];
 89 
 90                     System.Data.DataTable dt = new System.Data.DataTable();
 95 
 96                     //取表明赋值到dt TableName
 97                     dt.TableName = worksheet.Name;
 98 
 99                     worksheet.Columns.EntireColumn.AutoFit();
100 
101                     int row = worksheet.UsedRange.Rows.Count;
102                     int col = worksheet.UsedRange.Columns.Count;
103 
104                     for (int c = 1; c <= col; c++)
105                     {
106                         dt.Columns.Add(new DataColumn((String)((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c]).Text));
107                     }
108                     //添加一样式列
109                     dt.Columns.Add(new DataColumn("Style"));
110 
111                     for (int r = 2; r <= row; r++)
112                     {
113                         DataRow newRow = dt.NewRow();
114                         for (int c = 1; c <= col; c++)
115                         {
116                             rangecell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r, c];
117                             newRow[c - 1] = rangecell.Text;
118                             if (c == 3)
119                             {
120                                 //取信息的字体颜色与背景颜色
121                                 newRow[col] = rangecell.Font.Color + "|" + rangecell.Interior.Color;
122                             }
123                         }
124                         dt.Rows.Add(newRow);
125                     }
126                     ds.Tables.Add(dt);
127                 }
128             }
129             catch (Exception ex)
130             {
131                 throw (ex);
132             }
133             finally
134             {
135                 if (worksheet != null)
136                 {
137                     System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
138                     worksheet = null;
139                     System.Runtime.InteropServices.Marshal.ReleaseComObject(rangecell);
140                     rangecell = null;
141                 }
142                 excel.Workbooks.Close();
143                 excel.Quit();
144                 int generation = System.GC.GetGeneration(excel);
145                 if (excel != null)
146                 {
147                     System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
148                     excel = null;
149                 }
150                 System.GC.Collect(generation);
151             }
152             return ds;
153         }
154
341 
342     }
343 }

导出设置其样式

  1  public void ExportExcel(string[] listTableName, string[] listColName, List<List<Ad>> resource, string exporFilePath)
  2         {
  3             List<Ad> list = null;
  4             List<Ad> listresource = null;
  5             Microsoft.Office.Interop.Excel.Application app =
  6                new Microsoft.Office.Interop.Excel.ApplicationClass();
  7 
  8             app.Visible = false;
  9             app.ScreenUpdating = false;
 10             app.DisplayAlerts = false;
 11             Workbook wBook = app.Workbooks.Add(true);
 12 
 13             InsertLinkWorksheet(app, wBook);
 14 
 15             Worksheet wSheet = null;
 16             Range rangeResource = null;
 17             Range rangeContent = null;
 18             Range rangeTitle = null;
 19             //for (int k = 0; k < listList.Count; k++)
 20             for (int k = listList.Count - 1; k >= 0; k--)
 21             {
23 listresource = resource[k];//已排好的数据 24 wSheet = wBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet; 25 wSheet.Name = listTableName[k]; 26 27 try 28 { 29 int resourcenum = listresource.Count; 30 if (resourcenum > 0) 31 { 32 for (int i = 0; i < resourcenum; i++) 33 { 34 wSheet.Cells[i + 2, 1] = (i + 1); 35 wSheet.Cells[i + 2, 2] = listresource[i].ID; 36 wSheet.Cells[i + 2, 3] = listresource[i].Info; 37 wSheet.Cells[i + 2, 4] = listresource[i].Format; 38 wSheet.Cells[i + 2, 5] = listresource[i].Times; 39 wSheet.Cells[i + 2, 6] = listresource[i].Attach; 40 wSheet.Cells[i + 2, 7] = listresource[i].Frequency; 41 wSheet.Cells[i + 2, 8] = listresource[i].Same; 42 wSheet.Cells[i + 2, 9] = listresource[i].Dif; 43 wSheet.Cells[i + 2, 10] = listresource[i].Balanced; 44 45 if (listresource[i].RowStyle.Split('|')[0] != "") 46 { 47 rangeResource = wSheet.get_Range(wSheet.Cells[i + 2, 1], wSheet.Cells[i + 2, 10]); 48 rangeResource.Font.Color = listresource[i].RowStyle.Split('|')[0]; 49 rangeResource.Interior.Color = listresource[i].RowStyle.Split('|')[1]; 50 } 51 } 52 } 53 78 int m = 0; 79 int col = listColName.Count(); 80 for (m = 0; m < col; m++) 81 { 82 string headname = listColName[m];//单元格头部 83 wSheet.Cells[1, 1 + m] = headname; 84 } 85 //内容 86 rangeContent = wSheet.get_Range(wSheet.Cells[2, 1], wSheet.Cells[list.Count + resourcenum + 2 + 1, col]); 87 rangeContent.Borders.Color = System.Drawing.Color.Black.ToArgb(); 88 //rangeContent.Interior.Color = 10092543; //设置区域背景色 89 rangeContent.VerticalAlignment = -4108;//竖向居中 90 rangeContent.HorizontalAlignment = -4108;//横向居中 91 rangeContent.RowHeight = 18; 92 rangeContent.EntireColumn.AutoFit();//自动调整列宽 93 //标题 94 rangeTitle = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, col]); 95 rangeTitle.Borders.Color = System.Drawing.Color.Black.ToArgb(); 96 rangeTitle.Interior.Color = 65280; //设置区域背景色 97 rangeTitle.VerticalAlignment = -4108; 98 rangeTitle.HorizontalAlignment = -4108; 99 rangeTitle.RowHeight = 18; 100 rangeTitle.EntireColumn.AutoFit(); 101 //冻结首行 102 //rangeTitle.Select(); 103 app.ActiveWindow.SplitColumn = 0; 104 app.ActiveWindow.SplitRow = 1; 105 app.ActiveWindow.FreezePanes = true; 106 //rangeTitle.Font.Bold = true; //设置字体粗体。 107 } 108 catch (Exception err) 109 { 110 throw err; 111 } 112 finally 113 { 114 115 } 116 } 117 //设置禁止弹出保存和覆盖的询问提示框 118 app.DisplayAlerts = false; 119 app.AlertBeforeOverwriting = false; 120 ((Worksheet)wBook.Worksheets["Sheet1"]).Delete(); 121 try 122 { 123 wBook.Saved = true; 124 //保存工作簿 125 System.Reflection.Missing miss = System.Reflection.Missing.Value; 126 wBook.SaveAs(exporFilePath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss); 127 } 128 catch (Exception ex) 129 { 130 throw ex; 131 } 132 133 if (rangeResource != null) 134 { 135 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeResource); 136 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeContent); 137 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeTitle); 138 rangeResource = null; 139 rangeContent = null; 140 rangeTitle = null; 141 } 142 143 if (wSheet != null) 144 { 145 System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet); 146 wSheet = null; 147 } 148 149 if (wBook != null) 150 { 151 System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); 152 wBook = null; 153 } 154 app.Workbooks.Close(); 155 app.Quit(); 156 int generation = System.GC.GetGeneration(app); 157 if (app != null) 158 { 159 System.Runtime.InteropServices.Marshal.ReleaseComObject(app); 160 app = null; 161 } 162 GC.Collect(generation); 163 }

listTableName为多个sheet的名称

listColName为每页的列名

resource为sheet数据队列

exporFilePath为要保存的路径

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PL/SQLDeveloper导入导出Oracle数据库方法
前一篇博客介绍了Navicat工具备份Oracle的方法,这篇博客介绍一下使用PL/SQL Developer工具导入导出Oracle数据库的方法。 PL/SQL Developer是Oracle数据库用于导入导出数据库的主要工具之一,本文主要介绍利用PL/SQL导入导出Oracle数据库的过程。
1213 0
mysql 数据库导入\导出(总结备忘)
<div style="font-family:'lucida Grande',Verdana,'Microsoft YaHei'; font-size:14px; line-height:23.8px"> <div class="showContent" style="padding:0px; margin:20px 15px 8px; line-height:2; border-bo
3729 0
将Excel导入到DataTable (用ODBC方法连接)
///         /// 将Excel导入到DataTable (用ODBC方法连接)(LiPu)         ///         /// excel 路径         ///         ///         public DataTable Excel...
767 0
excel文件内容导入数据库的问题及解决
今天需要导一些数据,从excel导入到数据库中。 没有装现成的plsqldev,只能用sql*loader来弄了。 首先我把excel文件的内容转换成csv文件,以逗号分隔,在另存外excel文件的时候有那个选项。
1106 0
Windows DOS窗体下Oracle 数据库的导入导出命令
oracle 用户创建 数据库的导入导出imp/exp 可以在SQLPLUS.EXE或者DOS(命令行)中执行 执行环境:可以在SQLPLUS.EXE或者DOS(命令行)中执行, DOS中可以执行时由于 在oracle 8i 中 安装目录ora81BIN被设置为全局路径, 该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。 创建用户 给用户增加导入数据权限的操作
2039 0
Thinkphp5.0 PHPExcel 数据表格导出导入
1、先在github里面下载PHPexcel这个类库 或者通过以下链接下载PHPexcel类库。 http://www.php.cn/xiazai/leiku/1491 2、解压之后把它复制到extend里面 控制器代码如下: /** * Created by PhpStorm.
1794 0
10w行级别数据的Excel导入优化记录,优秀
10w行级别数据的Excel导入优化记录,优秀
8 0
+关注
james8888
专注商城、APP架构设计及开发,主导高并发分布式负载均衡、Hadoop批处理、Spark流式实时大数据处理架构设计,公众号[一个码农的日常]
272
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载