public class ExportToExcel
{
public void Export(List<MaterialExportModel> dataList, string newFilePath, ExcelPackage package = null)
{
if (package == null)
{
FileInfo newFile = new FileInfo(newFilePath);
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(newFilePath);
}
package = new ExcelPackage(newFile);
}
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("材料表");
//Add the headers
int num = 1;
int rowIndex = 1;
for (int i = 1; i <= 20; i++)
{
worksheet.Column(i).Width = 6;
}
if (dataList != null && dataList.Count > 0)
{
if (rowIndex == 1)
{
worksheet.Cells[rowIndex, 1, rowIndex + 1, 1].Merge = true;
worksheet.Cells[rowIndex, 1].Value = "标号";
worksheet.Cells[rowIndex, 2, rowIndex+1, 7].Merge = true;
worksheet.Cells[rowIndex, 2].Value = "名称规格";
worksheet.Cells[rowIndex, 8, rowIndex+1, 8].Merge = true;
worksheet.Cells[rowIndex, 8].Value = "数量";
worksheet.Cells[rowIndex, 9, rowIndex+1,11].Merge = true;
worksheet.Cells[rowIndex, 9].Value = "材质";
worksheet.Cells[rowIndex, 12, rowIndex , 13].Merge = true;
worksheet.Cells[rowIndex, 12].Value = "质量:(kg)";
worksheet.Cells[rowIndex+1, 12].Value = "单质量";
worksheet.Cells[rowIndex+1, 13].Value = "合质量";
// double subTotalWeight = item.Weight * item.Count;
worksheet.Cells[rowIndex, 14, rowIndex+1, 17].Merge = true;
worksheet.Cells[rowIndex, 14].Value = "备注";
rowIndex+=2;
}
//partDataList.Sort(delegate(ElementData x,ElementData y) {
// if (int.Parse(x.PartNo) > int.Parse(y.PartNo)) {
// return 1;
// }
// if (int.Parse(x.PartNo) < int.Parse(y.PartNo)) {
// return -1;
// }
// else
// {
// return 0;
// }
//});
foreach (var item in dataList)
{
worksheet.Cells[rowIndex, 1].Value = item.PipeNumber;
worksheet.Cells[rowIndex, 2, rowIndex, 7].Merge = true;
worksheet.Cells[rowIndex, 2].Value = item.PipeName;
worksheet.Cells[rowIndex, 8, rowIndex, 8].Merge = true;
worksheet.Cells[rowIndex, 8].Value = item.PipeCount;
worksheet.Cells[rowIndex, 9, rowIndex, 11].Merge = true;
worksheet.Cells[rowIndex, 9].Value = item.PipeSection;
worksheet.Cells[rowIndex, 12].Value = item.UnitWeight;
worksheet.Cells[rowIndex, 13].Value = item.PipeWeight;
worksheet.Cells[rowIndex, 14, rowIndex, 17].Merge = true;
worksheet.Cells[rowIndex, 14].Value = item.Note;
// totalWeight = totalWeight + subTotalWeight;
num++;
rowIndex++;
}
SetExcelHeadCellStyle(worksheet.Cells[1, 1, rowIndex - 1, 17], false);
}
for (int z = 1; z < rowIndex; z++)
{
worksheet.Row(z).Height = worksheet.Row(z).Height * 2;
}
package.Save();
}
/// <summary>
/// Excel 标题样式
/// </summary>
/// <param name="cells"></param>
/// <param name="isbold">字体是否加粗</param>
public static void SetExcelHeadCellStyle(ExcelRange cells, bool isbold = true)
{
cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cells.Style.WrapText = true;
cells.Style.Font.Bold = isbold;
}
#region NPOI DataGridView 导出 EXCEL
/// <summary>
/// NPOI DataGridView 导出 EXCEL
/// 03版Excel-xls最大行数是65536行,最大列数是256列
/// 07版Excel-xlsx最大行数是1048576行,最大列数是16384列
/// </summary>
/// <param name="fileName">默认保存文件名</param>
/// <param name="dgv">DataGridView</param>
/// <param name="fontname">字体名称</param>
/// <param name="fontsize">字体大小</param>
//将集合转化为DataTable
/// <summary>
/// 传入表头的集合和要生成表的数据集合生成一个DataTable
/// </summary>
/// <param name="materialModels">传入要生成DataTable的数据集合 </param>
/// <returns>返回一个DataTable </returns>
/// <param name="headName">传入要生成表头集合 </param>
public DataTable ToDT(List<MaterialExportModel> materialModels, List<string> headName)
{
DataTable dt = new DataTable();
if (materialModels != null && headName != null)
{
List<string> propNames = new List<string>();
List<PropertyInfo> propertyInfos = materialModels[0].GetType().GetProperties().ToList();
foreach (PropertyInfo p in propertyInfos)
{
propNames.Add(p.Name);
}
//根据表头生成数据列
foreach (var h in headName)
{
dt.Columns.Add(new DataColumn(h, typeof(string)));
}
//生成数据行
foreach (var m in materialModels)
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < headName.Count; i++)
{
dataRow[headName[i]] = propertyInfos[i].GetValue(m);
}
dt.Rows.Add(dataRow);
}
}
return dt;
}
//使用ASPOSE导出EXCEL
public bool ExportExcelWithAspose(DataTable data)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel (*.XLSX)|*.xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
try
{
if (data == null)
{
return false;
}
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(); //创建工作簿
Aspose.Cells.Worksheet sheet = book.Worksheets[0]; //创建工作表
sheet.Name = "管道信息表";
Cells cells = sheet.Cells; //单元格
//创建样式
Aspose.Cells.Style style = book.Styles[book.Styles.Add()];
style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线
style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线
style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线
style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线
style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
style.Font.Name = "宋体"; //字体
//style1.Font.IsBold = true; //设置粗体
style.Font.Size = 11; //设置字体大小
//style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景色
//style.Pattern = Aspose.Cells.BackgroundType.Solid;
int Colnum = data.Columns.Count;//表格列数
int Rownum = data.Rows.Count;//表格行数
//生成行 列名行
for (int i = 0; i < Colnum; i++)
{
cells[0, i].PutValue(data.Columns[i].ColumnName); //添加表头
cells[0, i].SetStyle(style); //添加样式
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据
cells[1 + i, k].SetStyle(style); //添加样式
}
}
sheet.AutoFitColumns(); //自适应宽
book.Save(saveFileDialog.FileName); //保存
CustomMessageBox.Show("导出成功","提示",CustomMessageBoxButton.OK,CustomMessageBoxIcon.Information);
GC.Collect();
}
catch (Exception e)
{
Logger.Instance.Error(e);
return false;
}
}
return true;
}
//DataGridView导出EXCEL
public void ExportExcel(string fileName, System.Windows.Forms.DataGridView dgv, string fontname, short fontsize)
{
IWorkbook workbook;
ISheet sheet;
Stopwatch sw = null;
//判断datagridview中内容是否为空
if (dgv.Rows.Count == 0)
{
MessageBox.Show("DataGridView中内容为空,请先导入数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//保存文件
string saveFileName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = "xls";
saveFileDialog.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
saveFileDialog.RestoreDirectory = true;
saveFileDialog.Title = "Excel文件保存路径";
saveFileDialog.FileName = fileName;
MemoryStream ms = new MemoryStream(); //MemoryStream
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//**程序开始计时**//
sw = new Stopwatch();
sw.Start();
saveFileName = saveFileDialog.FileName;
//检测文件是否被占用
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件被占用,请关闭文件" + saveFileName);
workbook = null;
ms.Close();
ms.Dispose();
return;
}
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
//*** 根据扩展名xls和xlsx来创建对象
string fileExt = Path.GetExtension(saveFileName).ToLower();
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
//***
//创建Sheet
if (workbook != null)
{
sheet = workbook.CreateSheet("Sheet1");//Sheet的名称
}
else
{
return;
}
//设置单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//水平居中对齐和垂直居中对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置字体
IFont font = workbook.CreateFont();
font.FontName = fontname;//字体名称
font.FontHeightInPoints = fontsize;//字号
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
cellStyle.SetFont(font);
//添加列名
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
//隐藏行列不导出
if (dgv.Columns[i].Visible == true)
{
headRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
headRow.GetCell(i).CellStyle = cellStyle;
}
}
//根据类型写入内容
for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
{
///跳过第一行,第一行为列名
IRow dataRow = sheet.CreateRow(rowNum + 1);
for (int columnNum = 0; columnNum < dgv.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //列宽
//隐藏行列不导出
if (dgv.Rows[rowNum].Visible == true && dgv.Columns[columnNum].Visible == true)
{
//防止行列超出Excel限制
if (fileExt == ".xls")
{
//03版Excel最大行数是65536行,最大列数是256列
if (rowNum > 65536)
{
MessageBox.Show("行数超过Excel限制!");
return;
}
if (columnNum > 256)
{
MessageBox.Show("列数超过Excel限制!");
return;
}
}
else if (fileExt == ".xlsx")
{
//07版Excel最大行数是1048576行,最大列数是16384列
if (rowNum > 1048576)
{
MessageBox.Show("行数超过Excel限制!");
return;
}
if (columnNum > 16384)
{
MessageBox.Show("列数超过Excel限制!");
return;
}
}
ICell cell = dataRow.CreateCell(columnNum);
if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull"))
{
cell.SetCellValue("");
}
}
//设置列宽
IRow currentRow;
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length + 10; //设置列宽数值
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
//单元格样式
dataRow.GetCell(columnNum).CellStyle = cellStyle;
}
}
}
//保存为Excel文件
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
//**程序结束计时**//
sw.Stop();
double totalTime = sw.ElapsedMilliseconds / 1000.0;
MessageBox.Show(fileName + " 导出成功\n耗时" + totalTime + "s", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
#endregion
#region 检测文件是否被占用
/// <summary>
/// 判定文件是否打开
/// </summary>
[DllImport("kernel32.dll")]
public static extern IntPtr _lopen(string lpPathName, int iReadWrite);
[DllImport("kernel32.dll")]
public static extern bool CloseHandle(IntPtr hObject);
public const int OF_READWRITE = 2;
public const int OF_SHARE_DENY_NONE = 0x40;
public readonly IntPtr HFILE_ERROR = new IntPtr(-1);
/// <summary>
/// 检测文件被占用
/// </summary>
/// <param name="FileNames">要检测的文件路径</param>
/// <returns></returns>
public bool CheckFiles(string FileNames)
{
if (!File.Exists(FileNames))
{
//文件不存在
return true;
}
IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE);
if (vHandle == HFILE_ERROR)
{
//文件被占用
return false;
}
//文件没被占用
CloseHandle(vHandle);
return true;
}
#endregion
}
EXCEL导出封装 C#
最新推荐文章于 2023-07-11 14:27:42 发布