C#导出Excel

 

 

private void btn_OutExcel_Click(object sender, EventArgs e)
{
string localFilePath = "";
string fileName = "库存信息" + DateTime.Now.ToString("yyyyMMdd");
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "保存文本文档";
saveFileDialog.DefaultExt = "xls";
saveFileDialog.Filter = "Excel文件|*.xls";
saveFileDialog.FileName = fileName;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
localFilePath = saveFileDialog.FileName.ToString();
}
ExcelHelper excelHelper = new ExcelHelper();
excelHelper.CreateOrderTable(gv_InvertoryBill, localFilePath, fileName, "Stock");
}

 

 

public void CreateOrderTable(DataGridView dgv,string localFilePath, string fileName,string type)
{
DataTable table = new DataTable();
for (int i = 0; i < dgv.Columns.Count; i++)
{
DataColumn dc = new DataColumn(dgv.Columns[i].Name.ToString());
table.Columns.Add(dc);
}
for (int j = 0; j < dgv.Rows.Count; j++)
{
DataRow dr = table.NewRow();
for (int h = 0; h < dgv.Columns.Count; h++)
{
dr[h] = Convert.ToString(dgv.Rows[j].Cells[h].Value);
}
table.Rows.Add(dr);
}
ExportExcel(table,localFilePath,fileName,type);
}

 

public void ExportExcel(DataTable table, string localFilePath, string fileName, string type)
{
IWorkbook workbook = new HSSFWorkbook();
//创建excel单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//设置字体
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "楷体";
cellStyle.SetFont(font);
//创建表
if (type == "Bill")
{
ISheet sheet = workbook.CreateSheet(fileName);
//设置第一列的宽度
sheet.SetColumnWidth(0, 10 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);

//创建一行
IRow row0 = sheet.CreateRow(0);
//创建四个单元格
ICell rowOicell0 = row0.CreateCell(0);
ICell rowOicell1 = row0.CreateCell(1);
ICell rowOicell2 = row0.CreateCell(2);
ICell rowOicell3 = row0.CreateCell(3);
ICell rowOicell4 = row0.CreateCell(4);
ICell rowOicell5 = row0.CreateCell(5);

rowOicell0.SetCellValue("序号");
rowOicell0.CellStyle = cellStyle;
rowOicell1.SetCellValue("代码");
rowOicell1.CellStyle = cellStyle;
rowOicell2.SetCellValue("名称");
rowOicell2.CellStyle = cellStyle;
rowOicell3.SetCellValue("库存数量");
rowOicell3.CellStyle = cellStyle;
rowOicell4.SetCellValue("订单数量");
rowOicell4.CellStyle = cellStyle;
rowOicell5.SetCellValue("入库数量");
rowOicell5.CellStyle = cellStyle;
for (int i = 1; i < table.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
ICell rowlicell0 = row.CreateCell(0);
ICell row1icell1 = row.CreateCell(1);
ICell row1icell2 = row.CreateCell(2);
ICell row1icell3 = row.CreateCell(3);
ICell row1icell4 = row.CreateCell(4);
ICell row1icell5 = row.CreateCell(5);

rowlicell0.SetCellValue(i);
rowlicell0.CellStyle = cellStyle;
row1icell1.SetCellValue(table.Rows[i]["Code"].ToString());
row1icell1.CellStyle = cellStyle;
row1icell2.SetCellValue(table.Rows[i]["Name"].ToString());
row1icell2.CellStyle = cellStyle;
row1icell3.SetCellValue(table.Rows[i]["InventoryCount"].ToString());
row1icell3.CellStyle = cellStyle;
row1icell4.SetCellValue(table.Rows[i]["WaitSortingCount"].ToString());
row1icell4.CellStyle = cellStyle;
row1icell5.SetCellValue(table.Rows[i]["ExportCount"].ToString());
row1icell5.CellStyle = cellStyle;
}
}
else if (type == "Stock")
{
ISheet sheet = workbook.CreateSheet(fileName);
//设置第一列的宽度
sheet.SetColumnWidth(0, 10 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);

//创建一行
IRow row0 = sheet.CreateRow(0);
//创建四个单元格
ICell rowOicell0 = row0.CreateCell(0);
ICell rowOicell1 = row0.CreateCell(1);
ICell rowOicell2 = row0.CreateCell(2);
ICell rowOicell3 = row0.CreateCell(3);
ICell rowOicell4 = row0.CreateCell(4);
rowOicell0.SetCellValue("序号");
rowOicell0.CellStyle = cellStyle;
rowOicell1.SetCellValue("代码");
rowOicell1.CellStyle = cellStyle;
rowOicell2.SetCellValue("名称");
rowOicell2.CellStyle = cellStyle;
rowOicell3.SetCellValue("库存数量");
rowOicell3.CellStyle = cellStyle;
rowOicell4.SetCellValue("更新时间");
rowOicell4.CellStyle = cellStyle;
for (int i = 1; i < table.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
ICell rowlicell0 = row.CreateCell(0);
ICell row1icell1 = row.CreateCell(1);
ICell row1icell2 = row.CreateCell(2);
ICell row1icell3 = row.CreateCell(3);
ICell row1icell4 = row.CreateCell(4);

rowlicell0.SetCellValue(i);
rowlicell0.CellStyle = cellStyle;
row1icell1.SetCellValue(table.Rows[i]["Code"].ToString());
row1icell1.CellStyle = cellStyle;
row1icell2.SetCellValue(table.Rows[i]["Name"].ToString());
row1icell2.CellStyle = cellStyle;
row1icell3.SetCellValue(table.Rows[i]["InventoryCount"].ToString());
row1icell3.CellStyle = cellStyle;
row1icell4.SetCellValue(table.Rows[i]["UpdateDate"].ToString());
row1icell4.CellStyle = cellStyle;
}
}
try
{
using (FileStream filestream = File.OpenWrite(localFilePath))
{
workbook.Write(filestream);
MessageBox.Show("创建成功！");
workbook.Close();
}
}
catch (Exception)
{
MessageBox.Show("请先关闭已打开的"+fileName+"文档");
}

}