///
本功能实现将程序中datagridview中的数据表导出到本地的excel
///
</summary>
///
<param name="dgv">
datagridview的名称
</param>
///
<param name="localFilePath">
当地要保存的路径
</param>
///
<param name="tableName">
保存的表名
</param>
public
void
Ex_Excel(DataGridView dgv,
string
localFilePath,
string
tableName)
HSSFWorkbook workbook
=
new
HSSFWorkbook();
MemoryStream ms
=
new
MemoryStream();
//
表单名
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(
"
Sheet1
"
);
//
DataGridView行数
int
rowCount =
dgv.Rows.Count;
//
DataGridView列数
int
colCount =
dgv.Columns.Count;
//
数据表格样式
NPOI.SS.UserModel.CellStyle dataStyle =
workbook.CreateCellStyle();
dataStyle.BorderBottom
=
NPOI.SS.UserModel.CellBorderType.THIN;
dataStyle.BorderLeft
=
NPOI.SS.UserModel.CellBorderType.THIN;
dataStyle.BorderTop
=
NPOI.SS.UserModel.CellBorderType.THIN;
dataStyle.BorderRight
=
NPOI.SS.UserModel.CellBorderType.THIN;
//
标题表格样式
NPOI.SS.UserModel.CellStyle titleStyle =
workbook.CreateCellStyle();
titleStyle.BorderBottom
=
NPOI.SS.UserModel.CellBorderType.THIN;
titleStyle.BorderLeft
=
NPOI.SS.UserModel.CellBorderType.THIN;
titleStyle.BorderTop
=
NPOI.SS.UserModel.CellBorderType.THIN;
titleStyle.BorderRight
=
NPOI.SS.UserModel.CellBorderType.THIN;
NPOI.SS.UserModel.Font font
=
workbook.CreateFont();
font.Boldweight
=
10
;
font.FontHeightInPoints
=
16
;
titleStyle.SetFont(font);
titleStyle.Alignment
=
NPOI.SS.UserModel.HorizontalAlignment.CENTER;
//
数据表标题
NPOI.SS.Util.CellRangeAddress rangTitle =
new
NPOI.SS.Util.CellRangeAddress(
0
,
0
,
0
, colCount -
1
);
sheet.AddMergedRegion(rangTitle);
NPOI.SS.UserModel.Cell titleCel
= sheet.CreateRow(
0
).CreateCell(
0
);
titleCel.SetCellValue(tableName);
titleCel.CellStyle
=
titleStyle;
NPOI.SS.UserModel.Row colNameRow
= sheet.CreateRow(
1
);
for
(
int
i =
0
; i < colCount; i++
)
if
(dgv.Columns[i].Visible)
//
把表头存入
NPOI.SS.UserModel.Cell cel =
colNameRow.CreateCell(i);
cel.SetCellValue(dgv.Columns[i].HeaderText.ToString());
cel.CellStyle
=
dataStyle;
//
数据表数据
for
(
int
j =
0
; j < rowCount; j++
)
NPOI.SS.UserModel.Row dataRow
= sheet.CreateRow(j +
2
);
for
(
int
k =
0
; k < colCount; k++
)
if
(dgv.Columns[k].Visible && dgv.Rows[j].Cells[k].Value !=
null
)
//
把数据保存到二维数组
NPOI.SS.UserModel.Cell cel =
dataRow.CreateCell(k);
//
cel.SetCellValue(dgv.Rows[j].Cells[k].Value.ToString());
cel.SetCellValue(dgv.Rows[j].Cells[k].FormattedValue.ToString());
cel.CellStyle
=
dataStyle;
workbook.Write(ms);
FileStream file
=
new
FileStream(@localFilePath, FileMode.Create);
workbook.Write(file);
file.Close();
workbook
=
null
;
ms.Close();
ms.Dispose();
附：npoi是外部程序集。
一、npoi读取excle表操作：
using (FileStream stream = new FileStream(@"c:\客户资料.xls", FileMode.Open, FileAccess.Read))
HSSFWorkbook workbook = new HSSFWorkbook(stream);
MessageBox.Show(workbook.GetSheetName(0));
•读取字符串类型数据MessageBox.Show(sheet.GetRow(3).GetCell(4).StringCellValue);
读取数字类型数据NumericCellValue。
•判断单元格数据类型：读取GetCell(4).CellType，与HSSFCell类中定义的常量比较即可。
•判断使用区域：结束行号：LastRowNum
二、写入Excel
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
HSSFRow row = sheet.CreateRow(0);
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("Hello");
row.CreateCell(1, HSSFCell.CELL_TYPE_NUMERIC).SetCellValue(3.14);
using (FileStream stream = new FileStream(@"c:\1.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))
workbook.Write(stream);
