HSSFWorkbook ISheet导出使用
使用excel模板导出数据时,模板可填充的数据行有限,可通过ShiftRows插入行,
private void Export(){
try
{
if (gvList.FocusedRowHandle < 0) return;
DataRow dr = gvList.GetDataRow(gvList.FocusedRowHandle);
string applyID = dr["ApplyID"].ToString();
string strSql = @" SELECT a.*,b.*,c.PartName FROM WfAbnormalSignOffApplication a
INNER JOIN WfProcessExceptionHandling b ON a.AbnormalID = b.AbnormalID
INNER JOIN BaPartItems c ON c.PartID=b.PartID WHERE a.ApplyID='" + applyID + "'";
DataSet dsWfAbnormalApplication = myHelper.GetDs(strSql);
string outFile = "";
SaveFileDialog dlg = new SaveFileDialog();
dlg.Title = "保存文件";
dlg.FileName = "";
dlg.Filter = "xls文件|*.xls";
dlg.FileName = "制程异常處理單";
dlg.ValidateNames = true;
if (dlg.ShowDialog() == DialogResult.OK)
{
outFile = dlg.FileName;
}
if (outFile == "")
{
return;
}
String tempFile = System.AppDomain.CurrentDomain.BaseDirectory + @"\制程异常处理单.xls";
if (System.IO.File.Exists(tempFile))//检查模板是否存在
{
File.Copy(tempFile, outFile, true);//复制模板
//判断输出的文件是否为只读,如果只读,会导致调用FileMode.Open时出错,因此需要把输出文件的只读状态去掉。
if (File.GetAttributes(outFile).ToString().IndexOf("ReadOnly") != -1)
{
System.IO.File.SetAttributes(outFile, System.IO.FileAttributes.Normal);
}
}
else
{
DevExpress.XtraEditors.XtraMessageBox.Show("模板文件【制程异常处理单.xls】不存在");
return;
}
HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new FileStream(outFile, FileMode.Open));
ISheet ws = null;
for (int i = 0; i < 1; i++)
{
if (i > 0) { workbook.CloneSheet(0); }
}
for (int i = 0; i < 1; i++)
{
ws = (ISheet)workbook.GetSheetAt(i);
if (dsWfAbnormalApplication.Tables[0].Rows.Count < 1) return;
ws.GetRow(1).GetCell(1).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["ApplyID"].ToString());
ws.GetRow(1).GetCell(6).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["AbnormalID"].ToString());
ws.GetRow(2).GetCell(1).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["LineID"].ToString());
ws.GetRow(2).GetCell(6).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["BadQuantity"].ToString());
ws.GetRow(3).GetCell(1).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["PartID"].ToString());
ws.GetRow(3).GetCell(6).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["ConfirmationDate"].ToString());
ws.GetRow(4).GetCell(1).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["PartName"].ToString());
if (dsWfAbnormalApplication.Tables[0].Rows[0]["IsCarUpgraded"].ToString().Equals("1"))
{
ws.GetRow(4).GetCell(6).SetCellValue("是");
}
else
{
ws.GetRow(4).GetCell(6).SetCellValue("否");
}
ws.GetRow(5).GetCell(1).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["DiscoveryProcess"].ToString());
ws.GetRow(5).GetCell(6).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["CarSheet"].ToString());
ws.GetRow(7).GetCell(0).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["ExceptionDescription"].ToString());
ws.GetRow(8).GetCell(8).SetCellValue(dsWfAbnormalApplication.Tables[0].Rows[0]["Frequency"].ToString());
}
string str = @"SELECT * FROM WfAnalysis WHERE ApplyID='" + applyID + "'"+
@" SELECT a.*,b.F_Name,b.F_Mail FROM WfCountermeasure a
LEFT JOIN Sys_User b ON a.PersonLiable=b.F_ID WHERE a.ApplyID='" + applyID + "'";
DataSet ds = myHelper.GetDs(str);
//根本原因分析
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
ws.GetRow(9).GetCell(6).SetCellValue(Convert.ToDateTime(ds.Tables[0].Rows[0]["CDateTime"]).ToString("yyyy/MM/dd"));
if (j > 0)
{
var rowSource = ws.GetRow(11);
var rowStyle = rowSource.RowStyle;//获取当前行样式
ws.ShiftRows(11 + j, ws.LastRowNum, 1, true, false);
var rowInsert = ws.CreateRow(11 + j);
if (rowStyle != null)
rowInsert.RowStyle = rowStyle;
rowInsert.Height = rowSource.Height;
for (int col = 0; col < rowSource.LastCellNum; col++)
{
var cellsource = rowSource.GetCell(col);
var cellInsert = rowInsert.CreateCell(col);
var cellStyle = cellsource.CellStyle;
//设置单元格样式
if (cellStyle != null)
cellInsert.CellStyle = cellsource.CellStyle;
}
//合并单元格
//CellRangeAddress region = new CellRangeAddress(11 + j, 11 + j, 0, 6);
//ws.AddMergedRegion(region);
//CellRangeAddress region1 = new CellRangeAddress(11 + j, 11 + j, 7, 8);
//ws.AddMergedRegion(region1);
}
ws.GetRow((11 + j)).GetCell(0).SetCellValue(ds.Tables[0].Rows[j]["CauseAnalysis"].ToString());
}
//输出改善对策
for (int j = 0; j < ds.Tables[1].Rows.Count; j++)
{
int rows = 15 + ds.Tables[0].Rows.Count - 2;
ws.GetRow(rows-2).GetCell(6).SetCellValue(Convert.ToDateTime(ds.Tables[1].Rows[0]["CDateTime"]).ToString("yyyy/MM/dd"));
if (j > 0)
{
var rowSource = ws.GetRow(rows);
var rowStyle = rowSource.RowStyle;//获取当前行样式
ws.ShiftRows(rows + j, ws.LastRowNum, 1, true, false);
var rowInsert = ws.CreateRow(rows + j);
if (rowStyle != null)
rowInsert.RowStyle = rowStyle;
rowInsert.Height = rowSource.Height;
for (int col = 0; col < rowSource.LastCellNum; col++)
{
var cellsource = rowSource.GetCell(col);
var cellInsert = rowInsert.CreateCell(col);
var cellStyle = cellsource.CellStyle;
//设置单元格样式
if (cellStyle != null)
cellInsert.CellStyle = cellsource.CellStyle;
}
//合并单元格
CellRangeAddress region = new CellRangeAddress(rows + j, rows + j, 0, 6);
ws.AddMergedRegion(region);
}
ws.GetRow((rows + j)).GetCell(0).SetCellValue(ds.Tables[1].Rows[j]["Countermeasures"].ToString());
ws.GetRow((rows + j)).GetCell(7).SetCellValue(ds.Tables[1].Rows[j]["F_Mail"].ToString());
ws.GetRow((rows + j)).GetCell(8).SetCellValue(Convert.ToDateTime(ds.Tables[1].Rows[j]["CompletionDate"]).ToString("yyyy-MM-dd"));
}
//保存
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(outFile, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
DevExpress.XtraEditors.XtraMessageBox.Show("报表导出成功!", "提示");
}
}
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
return;
}
}