public static MemoryStream DataGridViewToExcel(DataGridView myDgv,
string strHeaderText)
2 {
3 HSSFWorkbook workbook =
new HSSFWorkbook();
4 HSSFSheet sheet =
(HSSFSheet)workbook.CreateSheet();
5
6 #region 右击文件 属性信息
7 {
8 DocumentSummaryInformation dsi =
PropertySetFactory.CreateDocumentSummaryInformation();
9 dsi.Company =
"NPOI";
10 workbook.DocumentSummaryInformation =
dsi;
11
12 SummaryInformation si =
PropertySetFactory.CreateSummaryInformation();
13 si.Author =
"文件作者信息";
//填加xls文件作者信息
14 si.ApplicationName =
"创建程序信息";
//填加xls文件创建程序信息
15 si.LastAuthor =
"最后保存者信息";
//填加xls文件最后保存者信息
16 si.Comments =
"作者信息";
//填加xls文件作者信息
17 si.Title =
"标题信息";
//填加xls文件标题信息
18 si.Subject =
"主题信息";
//填加文件主题信息
19 si.CreateDateTime =
System.DateTime.Now;
20 workbook.SummaryInformation =
si;
21 }
22 #endregion
23
24 HSSFCellStyle dateStyle =
(HSSFCellStyle)workbook.CreateCellStyle();
25 HSSFDataFormat format =
(HSSFDataFormat)workbook.CreateDataFormat();
26 dateStyle.DataFormat = format.GetFormat(
"yyyy-mm-dd");
27
28 //取得列宽
29 int[] arrColWidth =
new int[myDgv.Columns.Count];
30 foreach (DataGridViewColumn item
in myDgv.Columns)
31 {
32 arrColWidth[item.Index] = Encoding.GetEncoding(
936).GetBytes(item.HeaderText.ToString()).Length;
33 }
34 //可以在循环里面自己设置列宽
35 for (
int i =
0; i < myDgv.Rows.Count; i++
)
36 {
37 for (
int j =
0; j < myDgv.Columns.Count; j++
)
38 {
39 int intTemp = Encoding.GetEncoding(
936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
40 if (intTemp >
arrColWidth[j])
41 {
42 arrColWidth[j] = intTemp/
5;
43 }
44 }
45 }
46 int rowIndex =
0;
47 foreach (DataGridViewRow row
in myDgv.Rows)
48 {
49 #region 新建表，填充表头，填充列头，样式
50 if (rowIndex ==
65535 || rowIndex ==
0)
51 {
52 if (rowIndex !=
0)
53 {
54 sheet =
(HSSFSheet)workbook.CreateSheet();
55 }
56
57 #region 表头及样式
58 {
59 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(
0);
60 headerRow.HeightInPoints =
25;
61 headerRow.CreateCell(
0).SetCellValue(strHeaderText);
62
63 HSSFCellStyle headStyle =
(HSSFCellStyle)workbook.CreateCellStyle();
64 // headStyle.Alignment = CellHorizontalAlignment.CENTER;
65 HSSFFont font =
(HSSFFont)workbook.CreateFont();
66 font.FontHeightInPoints =
20;
67 font.Boldweight =
700;
68 headStyle.SetFont(font);
69 headerRow.GetCell(
0).CellStyle =
headStyle;
70 // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
71 //headerRow.Dispose();
72 }
73 #endregion
74
75
76 #region 列头及样式
77 {
78 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(
1);
79 HSSFCellStyle headStyle =
(HSSFCellStyle)workbook.CreateCellStyle();
80 //headStyle.Alignment = CellHorizontalAlignment.CENTER;
81 HSSFFont font =
(HSSFFont)workbook.CreateFont();
82 font.FontHeightInPoints =
10;
83 font.Boldweight =
700;
84 headStyle.SetFont(font);
85 foreach (DataGridViewColumn column
in myDgv.Columns)
86 {
87 headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
88 headerRow.GetCell(column.Index).CellStyle =
headStyle;
89
90 //设置列宽
91 sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] +
1) *
256);
92 }
93 // headerRow.Dispose();
94 }
95 #endregion
96
97 rowIndex =
2;
98 }
99 #endregion
100
101
102 #region 填充内容
103 HSSFRow dataRow =
(HSSFRow)sheet.CreateRow(rowIndex);
104 if (row.Index >
0)
105 {
106 foreach (DataGridViewColumn column
in myDgv.Columns)
107 {
108 HSSFCell newCell =
(HSSFCell)dataRow.CreateCell(column.Index);
109
110 string drValue = myDgv[column.Index, row.Index-
1].Value.ToString();
111
112 switch (column.ValueType.ToString())
113 {
114 case "System.String":
//字符串类型
115 newCell.SetCellValue(drValue);
116 break;
117 case "System.DateTime":
//日期类型
118 System.DateTime dateV;
119 System.DateTime.TryParse(drValue,
out dateV);
120 newCell.SetCellValue(dateV);
121
122 newCell.CellStyle = dateStyle;
//格式化显示
123 break;
124 case "System.Boolean":
//布尔型
125 bool boolV =
false;
126 bool.TryParse(drValue,
out boolV);
127 newCell.SetCellValue(boolV);
128 break;
129 case "System.Int16":
//整型
130 case "System.Int32":
131 case "System.Int64":
132 case "System.Byte":
133 int intV =
0;
134 int.TryParse(drValue,
out intV);
135 newCell.SetCellValue(intV);
136 break;
137 case "System.Decimal":
//浮点型
138 case "System.Double":
139 double doubV =
0;
140 double.TryParse(drValue,
out doubV);
141 newCell.SetCellValue(doubV);
142 break;
143 case "System.DBNull":
//空值处理
144 newCell.SetCellValue(
"");
145 break;
146 default:
147 newCell.SetCellValue(
"");
148 break;
149 }
150
151 }
152 }
153 else
154 { rowIndex--
; }
155 #endregion
156
157 rowIndex++
;
158 }
159 using (MemoryStream ms =
new MemoryStream())
160 {
161 workbook.Write(ms);
162 ms.Flush();
163 ms.Position =
0;
164
165 sheet.Dispose();
166 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
167 return ms;
168 }
169 }
2、读取excel到DataTable
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
//获取表头
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
3、批量导入大批量数据的写法( SqlBulkCopy )
private void btnImport_Click(object sender, EventArgs e)
{
string constr = System.Configuration.ConfigurationSettings.AppSettings["connstr"].ToString();
#region 选择文件模块
string path = "";
//初始化一个OpenFileDialog类
OpenFileDialog fileDialog = new OpenFileDialog();
//判断用户是否正确的选择了文件
if (fileDialog.ShowDialog() == DialogResult.OK)
{
//获取用户选择文件的后缀名
string extension = Path.GetExtension(fileDialog.FileName);
//声明允许的后缀名
string[] str = new string[] { ".xls" };
if (!((IList)str).Contains(extension))
{
MessageBox.Show("仅能上传xls格式的Excel文件！");
}
else
{
//获取用户选择的文件，并判断文件大小不能超过20K，fileInfo.Length是以字节为单位的
FileInfo fileInfo = new FileInfo(fileDialog.FileName);
if (fileInfo.Length > 204800000)
{
MessageBox.Show("上传的Excel不能大于200M");
}
else
{
path = fileDialog.FileName;
//在这里就可以写获取到正确文件后的代码了
}
}
}
#endregion
System.Diagnostics.Stopwatch timeWatch = System.Diagnostics.Stopwatch.StartNew();
DataTable dtnew = NPOIExcelHelper.Import(path);
DataTable ss = GetDt();
if (dt != null && dt.Rows.Count != 0)
{
int n = 2500;
//遍历每一行执行插入操作
foreach (DataRow item in dtnew.Rows)
{
ss.Rows.Add(n,2, item["专业名称"], item["专业代码"], DateTime.Now, 0);
n++;
}
}
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(constr, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = "COMM_ZZ_COURSE_CLASS";//数据库中的表名
sqlbulkcopy.WriteToServer(ss);
timeWatch.Stop();
MessageBox.Show("数据导入成功耗时为：" + timeWatch.Elapsed + "");
}
说明：本人属于新手写文章，水平有限，如果发现文章有错误的地方，欢迎指出，多谢各位朋友，希望大家共同进步
代码下载
[原创]Net实现Excel导入导出到数据库(附源码)
标签：
