public void TestCloneSheetMultipleTimes()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone");
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell(0);
cell.SetCellValue(new HSSFRichTextString("Clone_Test"));
//Clone the sheet multiple times
workbook.CloneSheet(0);
workbook.CloneSheet(0);
Assert.IsNotNull(workbook.GetSheet("Test Clone"));
Assert.IsNotNull(workbook.GetSheet("Test Clone (2)"));
Assert.AreEqual("Test Clone (3)", workbook.GetSheetName(2));
Assert.IsNotNull(workbook.GetSheet("Test Clone (3)"));
workbook.RemoveSheetAt(0);
workbook.RemoveSheetAt(0);
workbook.RemoveSheetAt(0);
workbook.CreateSheet("abc ( 123)");
workbook.CloneSheet(0);
Assert.AreEqual("abc (124)", workbook.GetSheetName(1));
}
public void TestBug50298()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
ISheet sheet = wb.CloneSheet(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)");
wb.SetSheetName(wb.GetSheetIndex(sheet), "copy");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy");
wb.SetSheetOrder("copy", 0);
assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received");
wb.RemoveSheetAt(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
// check that the overall workbook serializes with its correct size
int expected = wb.Workbook.Size;
int written = wb.Workbook.Serialize(0, new byte[expected * 2]);
Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected);
HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb);
assertSheetOrder(read, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
}
public static void SaveSOExcel(List <So> soList, List <List <SoItemsContentAndState> > soitemsListList)
{
if (soList.Count() != soitemsListList.Count())
{
MessageBox.Show("Internal Error. Please send the log file to the Author");
Logger.Error(soList.Count() + "," + soitemsListList.Count());
return;
}
FileStream file;
try
{
file = new FileStream(@"SoTemplate.dll", FileMode.Open, FileAccess.Read);
}
catch (Exception)
{
MessageBox.Show("Please check the SoTemplate.dll.");
return;
}
hssfworkbook = new HSSFWorkbook(file);
WriteDsiInfo();
for (int i = 0; i < soList.Count(); i++)
{
ISheet sheet = hssfworkbook.CloneSheet(0);
FillTheSoSheet(sheet, soList[i], soitemsListList[i]);
hssfworkbook.SetSheetName(3 + i, "SO" + i.ToString());
}
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
WriteToFile();
}
public void TestCloneSheet()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone");
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell(0);
Cell cell2 = row.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("Clone_Test"));
cell2.CellFormula = ("sin(1)");
NPOI.SS.UserModel.Sheet ClonedSheet = workbook.CloneSheet(0);
Row ClonedRow = ClonedSheet.GetRow(0);
//Check for a good Clone
Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test");
//Check that the cells are not somehow linked
cell.SetCellValue(new HSSFRichTextString("Difference Check"));
cell2.CellFormula = ("cos(2)");
if ("Difference Check".Equals(ClonedRow.GetCell(0).RichStringCellValue.String))
{
Assert.Fail("string cell not properly Cloned");
}
if ("COS(2)".Equals(ClonedRow.GetCell(1).CellFormula))
{
Assert.Fail("formula cell not properly Cloned");
}
Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test");
Assert.AreEqual(ClonedRow.GetCell(1).CellFormula, "SIN(1)");
}
public void TestCloneComment()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment c = p.CreateComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5));
c.Column = (1);
c.Row = (2);
c.String = (new HSSFRichTextString("qwertyuio"));
HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
HSSFPatriarch p2 = sh2.DrawingPatriarch as HSSFPatriarch;
HSSFComment c2 = (HSSFComment)p2.Children[0];
Assert.IsTrue(Arrays.Equals(c2.GetTextObjectRecord().Serialize(), c.GetTextObjectRecord().Serialize()));
Assert.IsTrue(Arrays.Equals(c2.GetObjRecord().Serialize(), c.GetObjRecord().Serialize()));
Assert.IsTrue(Arrays.Equals(c2.NoteRecord.Serialize(), c.NoteRecord.Serialize()));
//everything except spRecord.shapeId must be the same
Assert.IsFalse(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
EscherSpRecord sp = (EscherSpRecord)c2.GetEscherContainer().GetChild(0);
sp.ShapeId = (1025);
Assert.IsTrue(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
}
public void TestCloneWithProtect()
{
String passwordA = "secrect";
int expectedHashA = -6810;
String passwordB = "admin";
int expectedHashB = -14556;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet hssfSheet = (HSSFSheet)workbook.CreateSheet();
Assert.IsFalse(hssfSheet.ObjectProtect);
hssfSheet.ProtectSheet(passwordA);
Assert.IsTrue(hssfSheet.ObjectProtect);
Assert.AreEqual(expectedHashA, hssfSheet.Password);
Assert.AreEqual(expectedHashA, hssfSheet.Sheet.ProtectionBlock.PasswordHash);
// Clone the sheet, and make sure the password hash is preserved
HSSFSheet sheet2 = (HSSFSheet)workbook.CloneSheet(0);
Assert.IsTrue(hssfSheet.ObjectProtect);
Assert.AreEqual(expectedHashA, sheet2.Sheet.ProtectionBlock.PasswordHash);
// change the password on the first sheet
hssfSheet.ProtectSheet(passwordB);
Assert.IsTrue(hssfSheet.ObjectProtect);
Assert.AreEqual(expectedHashB, hssfSheet.Sheet.ProtectionBlock.PasswordHash);
Assert.AreEqual(expectedHashB, hssfSheet.Password);
// but the cloned sheet's password should remain unchanged
Assert.AreEqual(expectedHashA, sheet2.Password);
}
public void TestCloneSheetWithDrawings()
{
HSSFWorkbook wb1 = HSSFTestDataSamples.OpenSampleWorkbook("45720.xls");
HSSFSheet sheet1 = (HSSFSheet)wb1.GetSheetAt(0);
wb1.Workbook.FindDrawingGroup();
DrawingManager2 dm1 = wb1.Workbook.DrawingManager;
wb1.CloneSheet(0);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb2.Workbook.FindDrawingGroup();
DrawingManager2 dm2 = wb2.Workbook.DrawingManager;
//Check EscherDggRecord - a workbook-level registry of drawing objects
Assert.AreEqual(dm1.GetDgg().MaxDrawingGroupId + 1, dm2.GetDgg().MaxDrawingGroupId);
HSSFSheet sheet2 = (HSSFSheet)wb2.GetSheetAt(1);
//Check that id of the drawing Group was updated
EscherDgRecord dg1 = (EscherDgRecord)(sheet1.DrawingPatriarch as HSSFPatriarch).GetBoundAggregate().FindFirstWithId(EscherDgRecord.RECORD_ID);
EscherDgRecord dg2 = (EscherDgRecord)(sheet2.DrawingPatriarch as HSSFPatriarch).GetBoundAggregate().FindFirstWithId(EscherDgRecord.RECORD_ID);
int dg_id_1 = dg1.Options >> 4;
int dg_id_2 = dg2.Options >> 4;
Assert.AreEqual(dg_id_1 + 1, dg_id_2);
//TODO: Check shapeId in the Cloned sheet
}
/// <summary>
/// 增加一個新的表格，並將新增的表格作為活動表
/// </summary>
/// <param name="sheetName">表名稱</param>
public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
_currentSheet = _workbook.CloneSheet(i);
sheetIndex = _workbook.GetSheetIndex(_currentSheet);
_workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
_defaultExcelCellStyle = new ExcelCellStyle();
}
public void TestSheetClone()
{
// First up, try a simple file
HSSFWorkbook b = new HSSFWorkbook();
Assert.AreEqual(0, b.NumberOfSheets);
b.CreateSheet("Sheet One");
b.CreateSheet("Sheet Two");
Assert.AreEqual(2, b.NumberOfSheets);
b.CloneSheet(0);
Assert.AreEqual(3, b.NumberOfSheets);
// Now try a problem one with drawing records in it
b = OpenSample("SheetWithDrawing.xls");
Assert.AreEqual(1, b.NumberOfSheets);
b.CloneSheet(0);
Assert.AreEqual(2, b.NumberOfSheets);
}
public void TestCloneSheetBasic()
{
HSSFWorkbook b = new HSSFWorkbook();
ISheet s = b.CreateSheet("Test");
s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1));
ISheet clonedSheet = b.CloneSheet(0);
Assert.AreEqual(1, clonedSheet.NumMergedRegions, "One merged area");
}
private void button2_Click(object sender, EventArgs e)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("My Sheet");
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
ICellStyle style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;
style2.FillPattern = FillPatternType.SOLID_FOREGROUND;
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellStyle = style1;
cell.SetCellValue(0);
cell = sheet.CreateRow(1).CreateCell(0);
cell.CellStyle = style2;
cell.SetCellValue(1);
cell = sheet.CreateRow(2).CreateCell(0);
cell.CellStyle = style1;
cell.SetCellValue(2);
cell = sheet.CreateRow(3).CreateCell(0);
cell.CellStyle = style2;
cell.SetCellValue(3);
cell = sheet.CreateRow(4).CreateCell(0);
cell.CellStyle = style1;
cell.SetCellValue(4);
string saveFile = FileDialogHelper.SaveExcel("test.xls");
if (!string.IsNullOrEmpty(saveFile))
{
SaveToFile(workbook, saveFile);
if (MessageUtil.ShowYesNoAndTips("保存成功，是否打开文件？") == System.Windows.Forms.DialogResult.Yes)
{
System.Diagnostics.Process.Start(saveFile);
}
}
workbook.CloneSheet(0);
}
public new void Test35084()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet("Sheet1");
IRow r = s.CreateRow(0);
r.CreateCell(0).SetCellValue(1);
r.CreateCell(1).CellFormula = ("A1*2");
NPOI.SS.UserModel.ISheet s1 = wb.CloneSheet(0);
r = s1.GetRow(0);
Assert.AreEqual(r.GetCell(0).NumericCellValue, 1, 1); // sanity Check
Assert.IsNotNull(r.GetCell(1));
Assert.AreEqual(r.GetCell(1).CellFormula, "A1*2");
}
public void TestClone()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
String formula = "7";
HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);
HSSFConditionalFormattingRule rule2 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");
HSSFConditionalFormattingRule[] cfRules =
{
rule1, rule2
};
short col = 1;
CellRangeAddress[] regions =
{
new CellRangeAddress(0, 65535, col, col)
};
sheetCF.AddConditionalFormatting(regions, cfRules);
try
{
wb.CloneSheet(0);
}
catch (Exception e)
{
if (e.Message.IndexOf("needs to define a clone method") > 0)
{
throw new AssertionException("Indentified bug 45682");
}
throw e;
}
Assert.AreEqual(2, wb.NumberOfSheets);
}
public void TestPageBreakClones()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = b.CreateSheet("Test");
s.SetRowBreak(3);
s.SetColumnBreak((short)6);
NPOI.SS.UserModel.Sheet clone = b.CloneSheet(0);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken");
Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken");
s.RemoveRowBreak(3);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken");
}
public void TestBug57231()
{
HSSFWorkbook wb = HSSFTestDataSamples
.OpenSampleWorkbook("57231_MixedGasReport.xls");
HSSFSheet sheet = wb.GetSheet("master") as HSSFSheet;
HSSFSheet newSheet = wb.CloneSheet(wb.GetSheetIndex(sheet)) as HSSFSheet;
int idx = wb.GetSheetIndex(newSheet);
wb.SetSheetName(idx, "newName");
// Write the output to a file
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbBack);
Assert.IsNotNull(wbBack.GetSheet("master"));
Assert.IsNotNull(wbBack.GetSheet("newName"));
}
public void TestCloneSheet()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("HyperlinksOnManySheets.xls");
ICell cell;
IHyperlink link;
NPOI.SS.UserModel.ISheet sheet = wb.CloneSheet(0);
cell = sheet.GetRow(4).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/", link.Address);
cell = sheet.GetRow(8).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/hssf/", link.Address);
}
public static EmailExtend.FileAttachment SupervisorReport(string startTime, string endTime)
{
using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/supervisorsheet_citizenM.xls"), FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
List <SupervisorSheetModel_citizenM> ModelList = DbFunction.SupervisorListReport_citizenM(startTime, endTime);
var UserList = ModelList.Select(x => x.InspectBy).Distinct().ToList();
var datenow = Convert.ToDateTime(startTime);
//得到 Excel 模板的名称 为 Room 的表格
var sheet0 = (HSSFSheet)hssfworkbook.GetSheetAt(0);
sheet0.GetRow(6).GetCell(3).SetCellValue(ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime), 2) + "-" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(endTime), 2));
var tabIndex = 1;
for (int i = 0; i < UserList.Count; i++)
{
var row = 9;
sheet0 = (HSSFSheet)hssfworkbook.CloneSheet(0);
string sheetName = UserList[i].ToString().Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' ');
hssfworkbook.SetSheetName(tabIndex, sheetName);
var NodeList = ModelList.Where(x => x.InspectBy == sheetName).OrderBy(x => Convert.ToInt32(x.NodeName)).ThenBy(x => Convert.ToInt32(x.NodeName)).ToList();
for (int j = 0; j < NodeList.Count; j++)
{
var cleaningType = "";
switch (NodeList[j].CleaningType)
{
case 0:
cleaningType = "Checkout";
break;
case 1:
cleaningType = "Stayover";
break;
case 2:
cleaningType = "Mandatory Stayover";
break;
}
sheet0.CopyRow(8, row);
HSSFRow fsRow = (HSSFRow)sheet0.GetRow(row);
fsRow.GetCell(0).SetCellValue(NodeList[j].NodeName);
fsRow.GetCell(1).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoingTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(2).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(3).SetCellValue(NodeList[j].StartStatusRoom);
fsRow.GetCell(4).SetCellValue(NodeList[j].EndStatusRoom);
fsRow.GetCell(5).SetCellValue(cleaningType);
fsRow.GetCell(6).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoingTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(7).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(8).SetCellValue(NodeList[j].DoneBy);
fsRow.GetCell(9).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoingTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(10).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(11).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(12).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(13).SetCellValue(NodeList[j].InspectBy);
fsRow.GetCell(14).SetCellValue(NodeList[j].InspectRemark);
fsRow.GetCell(15).SetCellValue(NodeList[j].InspectTouchUpRemark);
fsRow.GetCell(16).SetCellValue(NodeList[j].RoomNote);
fsRow.GetCell(17).SetCellValue(NodeList[j].IsSkipped == 1 ? "√" : "");
row++;
}
sheet0.RemoveRowAt(8);
tabIndex++;
}
if (UserList.Count > 0)
{
hssfworkbook.RemoveSheetAt(0);
}
var fileName = SystemConfig.HotelName + " Supervisor Report_" + ExcelExtend.DatetTimeFormate(datenow) + ".xls";
var ms = new MemoryStream();
hssfworkbook.Write(ms);
return(new EmailExtend.FileAttachment()
{
FileContent = ms.ToArray(),
FileName = fileName
});
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
string strPrintId = this.strPrintId.Value;
string strCompanyName = "郑州市环保局";
TMisMonitorTaskVo objTask = new TMisMonitorTaskLogic().Details(strPrintId);
if (objTask.CLIENT_COMPANY_ID.Length > 0)
{
strCompanyName = new i3.BusinessLogic.Channels.Base.Company.TBaseCompanyInfoLogic().Details(objTask.CLIENT_COMPANY_ID).COMPANY_NAME;
}
TMisMonitorSubtaskVo objSubTask = new TMisMonitorSubtaskVo();
objSubTask.TASK_ID = strPrintId;
DataTable dtSub = new TMisMonitorSubtaskLogic().SelectByTable(objSubTask);
string strSampleIDs = "";
for (int i = 0; i < dtSub.Rows.Count; i++)
{
GetPoint_UnderTask(dtSub.Rows[i]["ID"].ToString(), ref strSampleIDs);
}
//获取基本信息
DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleIDs, "021", "0");
int iPageCount = dt.Rows.Count / 7;
if (dt.Rows.Count % 7 != 0)
{
iPageCount += 1;
}
FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/QHDSamplingCode.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//sheet复制
for (int k = 1; k < iPageCount; k++)
{
hssfworkbook.CloneSheet(0);
hssfworkbook.SetSheetName(k, "Sheet" + (k + 1).ToString());
}
for (int m = 1; m <= iPageCount; m++)
{
ISheet sheet = hssfworkbook.GetSheet("Sheet" + m.ToString());
sheet.GetRow(2).GetCell(0).SetCellValue("委托单位： " + strCompanyName);
sheet.GetRow(2).GetCell(4).SetCellValue("任务编号： " + objTask.TICKET_NUM);
DataTable dtNew = new DataTable();
dtNew = dt.Copy();
dtNew.Clear();
for (int n = (m - 1) * 7; n < m * 7; n++)
{
if (n >= dt.Rows.Count)
{
break;
}
dtNew.ImportRow(dt.Rows[n]);
}
for (int i = 0; i < dtNew.Rows.Count; i++)
{
sheet.GetRow(i + 4).GetCell(0).SetCellValue((i + 1).ToString());
if (objTask.SAMPLE_SOURCE == "抽样")
{
sheet.GetRow(i + 4).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_NAME"].ToString());
}
else
{
sheet.GetRow(i + 4).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_NAME"].ToString());
}
sheet.GetRow(i + 4).GetCell(3).SetCellValue(dtNew.Rows[i]["MONITOR_TYPE_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(4).SetCellValue(dtNew.Rows[i]["ITEM_NAME"].ToString());
}
}
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("水质样品交接记录表.xls", Encoding.UTF8));
curContext.Response.BinaryWrite(stream.GetBuffer());
curContext.Response.End();
}
}
public static EmailExtend.FileAttachment RoomScoreReport(string startTime, string endTime)
{
using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/cleanscore_citizenM.xls"), FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
var ModelList = DbFunction.RoomScoreReport_citizenM(startTime, endTime);
var sheetUser = (HSSFSheet)hssfworkbook.GetSheet("pm");
var summarySheet = (HSSFSheet)hssfworkbook.GetSheet("Summary");
sheetUser.GetRow(0).GetCell(18).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy"));
summarySheet.GetRow(0).GetCell(1).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy"));
int row = 2;
var tabIndex = 2;
var userList = ModelList.Select(x => new { x.UserID, x.UserName }).Distinct().ToList();
userList.ForEach(user => {
var dataRow = 3;
int copyRow = 2;
sheetUser = (HSSFSheet)hssfworkbook.CloneSheet(1);
string sheetName = user.UserName.Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' ');
hssfworkbook.SetSheetName(tabIndex, sheetName);
var RoomList = ModelList.AsParallel().Where(x => x.UserID == user.UserID).OrderBy(x => x.NodeName).ToList();
HSSFRow summaryTimeRow = (HSSFRow)summarySheet.GetRow(0);
summarySheet.CopyRow(copyRow, row + 1);
HSSFRow sRow = (HSSFRow)summarySheet.GetRow(row + 1);
sRow.GetCell(0).SetCellValue(user.UserName);
sRow.GetCell(1).SetCellValue(RoomList.Count());
sRow.GetCell(2).SetCellValue(RoomList.Sum(x => x.Credit));
RoomList.ForEach(item => {
var cleaningType = "";
switch (item.CleaningType)
{
case 0:
cleaningType = "Checkout";
break;
case 1:
cleaningType = "Stayover";
break;
case 2:
cleaningType = "Mandatory Stayover";
break;
}
HSSFRow userRow = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow);
userRow.GetCell(0).SetCellValue(item.NodeName);
userRow.GetCell(1).SetCellValue(item.StartRoomStauts);
userRow.GetCell(2).SetCellValue(item.EndRoomStauts);
userRow.GetCell(3).SetCellValue(cleaningType);
userRow.GetCell(4).SetCellValue(item.CreatorUserName);
userRow.GetCell(5).SetCellValue(item.UserName);
userRow.GetCell(6).SetCellValue(item.ToDoTime);
userRow.GetCell(7).SetCellValue(item.DoingTime);
userRow.GetCell(8).SetCellValue(item.PauseTime);
userRow.GetCell(9).SetCellValue(item.PauseReason);
userRow.GetCell(10).SetCellValue(item.ResumeTime);
userRow.GetCell(11).SetCellValue(item.DoneTime);
userRow.GetCell(12).SetCellValue(item.CleanTime);
userRow.GetCell(13).SetCellValue(item.Credit);
userRow.GetCell(14).SetCellValue(item.AdditionalTaskName);
userRow.GetCell(15).SetCellValue(item.AdditionalTaskCredit);
userRow.GetCell(16).SetCellValue(item.TouchUpStartTime);
userRow.GetCell(17).SetCellValue(item.TouchUpDoneTime);
userRow.GetCell(18).SetCellValue(item.TouchUpMinutes);
userRow.GetCell(19).SetCellValue(item.RoomNote);
dataRow++;
});
HSSFRow sheetLast = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow);
sheetLast.GetCell(11).SetCellValue("Total");
sheetLast.GetCell(12).SetCellValue(RoomList.Sum(x => x.Credit));
sheetUser.RemoveRowAt(2);
row++;
tabIndex++;
});
hssfworkbook.RemoveSheetAt(1);
summarySheet.RemoveRowAt(2);
var fileName = SystemConfig.HotelName + " Clean Score Report_" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime)) + ".xls";
var ms = new MemoryStream();
hssfworkbook.Write(ms);
return(new EmailExtend.FileAttachment()
{
FileContent = ms.ToArray(),
FileName = fileName
});
}
}
/// <summary>
/// 根据收货出账单号生成Execl
/// </summary>
/// <param name="bill"></param>
/// <param name="list"></param>
public static void ExeclCreate(ReceivingBill bill, List <BillModel> list)
{
Log.Info("LMS开始生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
var groupShipping = list.GroupBy(p => p.InShippingMethodName).Select(g => new
{
ShippingMethodName = g.Key,
TotalFee = g.Sum(p => p.Freight + p.FuelCharge +
p.Register + p.Surcharge +
p.TariffPrepayFee +
p.SpecialFee + p.RemoteAreaFee)
}).ToList();
using (
var file = new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate/FinancialCreditBill.xls", FileMode.Open,
FileAccess.Read))
{
IWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheetNew = hssfworkbook.CloneSheet(0);
sheetNew.GetRow(1).GetCell(0).SetCellValue("客户名称：{0}".FormatWith(bill.CustomerName));
sheetNew.GetRow(1).GetCell(1).SetCellValue("账单结算期间：{0}至{1}".FormatWith(DateTime.Parse(bill.BillStartTime).ToString("yyyy-MM-dd"), DateTime.Parse(bill.BillEndTime).ToString("yyyy-MM-dd")));
IRow sourceRow = sheetNew.GetRow(2);
int startRow = 2;
foreach (var t in groupShipping)
{
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow row = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref row);
row.GetCell(0).SetCellValue(t.ShippingMethodName);
row.GetCell(1).SetCellValue("总费用：{0}".FormatWith(t.TotalFee.ToString("F2")));
}
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow srow = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref srow);
srow.GetCell(0).SetCellValue("结算人：{0}".FormatWith(bill.ReceivingBillAuditor));
startRow = startRow + 3;
int startR = startRow;
foreach (var b in list)
{
IRow row = sheetNew.CreateRow(startRow);
row.CreateCell(0).SetCellValue(b.WayBillNumber);
row.CreateCell(1).SetCellValue(b.CustomerOrderNumber ?? "");
row.CreateCell(2).SetCellValue(b.CreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(3).SetCellValue(b.InStorageCreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(4).SetCellValue(b.TrackingNumber ?? "");
row.CreateCell(5).SetCellValue(b.ChineseName);
row.CreateCell(6).SetCellValue(b.InShippingMethodName);
row.CreateCell(7).SetCellValue(b.SettleWeight.ToString("F4"));
row.CreateCell(8).SetCellValue(b.Weight.ToString("F4"));
row.CreateCell(9).SetCellValue(b.CountNumber);
row.CreateCell(10).SetCellValue(double.Parse(b.Freight.ToString("F2")));
row.CreateCell(11).SetCellValue(double.Parse(b.Register.ToString("F2")));
row.CreateCell(12).SetCellValue(double.Parse(b.FuelCharge.ToString("F2")));
row.CreateCell(13).SetCellValue(double.Parse(b.Surcharge.ToString("F2")));
row.CreateCell(14).SetCellValue(double.Parse(b.TariffPrepayFee.ToString("F2")));
row.CreateCell(15).SetCellValue(double.Parse(b.SpecialFee.ToString("F2")));
row.CreateCell(16).SetCellValue(double.Parse(b.RemoteAreaFee.ToString("F2")));
startRow++;
row.CreateCell(17).SetCellFormula("sum(K" + startRow + ":Q" + startRow + ")");
}
IRow totalRow = sheetNew.CreateRow(startRow);
for (int i = 0; i < 18; i++)
{
totalRow.CreateCell(i).SetCellValue("");
}
startR++;
totalRow.Cells[9].SetCellValue("各项计费小计：");
totalRow.Cells[10].SetCellFormula("sum(K" + startR + ":K" + startRow + ")");
totalRow.Cells[11].SetCellFormula("sum(L" + startR + ":L" + startRow + ")");
totalRow.Cells[12].SetCellFormula("sum(M" + startR + ":M" + startRow + ")");
totalRow.Cells[13].SetCellFormula("sum(N" + startR + ":N" + startRow + ")");
totalRow.Cells[14].SetCellFormula("sum(O" + startR + ":O" + startRow + ")");
totalRow.Cells[15].SetCellFormula("sum(P" + startR + ":P" + startRow + ")");
totalRow.Cells[16].SetCellFormula("sum(Q" + startR + ":Q" + startRow + ")");
totalRow.Cells[17].SetCellFormula("sum(R" + startR + ":R" + startRow + ")");
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheetNew), bill.ReceivingBillID);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
if (File.Exists(Path.Combine(path, bill.ReceivingBillID + ".xls")))
{
File.Delete(Path.Combine(path, bill.ReceivingBillID + ".xls"));
}
var savefile = new FileStream(Path.Combine(path, bill.ReceivingBillID + ".xls"), FileMode.Create);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.Write(savefile);
file.Close();
}
Log.Info("LMS完成生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
}
public void DataTableToExcel()
{
int count = this.dt.Rows.Count;
int sheetCount = this.GetSheetCount(count);
DateTime now = DateTime.Now;
FileStream file = new FileStream(this.templetFile, FileMode.Open, FileAccess.Read);//读入excel模板
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet("Sheet1");
for (int i = 1; i < sheetCount; i++)
{
workbook.CloneSheet(0);
}
this.FillData(workbook, sheetCount);
try
{
FileStream fs = File.Create(this.outputFile);
workbook.Write(fs);
fs.Close();
}
catch (Exception exception)
{
throw exception;
}
//Application o = new ApplicationClass
//{
// Visible = false
//};
//DateTime time2 = DateTime.Now;
//Workbook workBook = o.Workbooks.Open(this.templetFile, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing);
//Worksheet worksheet = (Worksheet)workBook.Sheets.get_Item(1);
//for (int i = 1; i < sheetCount; i++)
//{
// worksheet.Copy(this.missing, workBook.Worksheets[i]);
//}
//this.FillData(workBook, sheetCount);
//worksheet.Activate();
//try
//{
// workBook.SaveAs(this.outputFile, this.missing, this.missing, this.missing, this.missing, this.missing, XlSaveAsAccessMode.xlExclusive, this.missing, this.missing, this.missing, this.missing, this.missing);
// workBook.Close(null, null, null);
// o.Workbooks.Close();
// o.Application.Quit();
// o.Quit();
// Marshal.ReleaseComObject(worksheet);
// Marshal.ReleaseComObject(workBook);
// Marshal.ReleaseComObject(o);
// worksheet = null;
// workBook = null;
// o = null;
// GC.Collect();
//}
//catch (Exception exception)
//{
// throw exception;
//}
//finally
//{
// Process[] processesByName = Process.GetProcessesByName("Excel");
// foreach (Process process in processesByName)
// {
// DateTime startTime = process.StartTime;
// if ((startTime > now) && (startTime < time2))
// {
// process.Kill();
// }
// }
//}
}
private void tsbExcel_Click(object sender, EventArgs e)
{
HSSFWorkbook workbook = null;
using (MemoryStream ms = new MemoryStream(Resource.template))
{
workbook = new HSSFWorkbook(ms); // 工作簿
}
Dictionary <string, HSSFCellStyle> styleDic = new Dictionary <string, HSSFCellStyle>();
Dictionary <string, IFont> fontDic = new Dictionary <string, IFont>();
//准备sheet
var sheetIndex = 0;
foreach (I3ReportData rd in reportDatas.Datas)
{
if (sheetIndex > 0)
{
workbook.CloneSheet(0);
}
sheetIndex++;
}
sheetIndex = 0;
foreach (I3ReportData rd in reportDatas.Datas)
{
string sheetName = string.IsNullOrEmpty(rd.Name) ? ("sheet" + (sheetIndex + 1).ToString()) : rd.Name;
//ISheet sheet = workbook.CreateSheet(sheetName);
ISheet sheet = workbook.GetSheetAt(sheetIndex);
workbook.SetSheetName(sheetIndex, sheetName);
//设置列宽
var colIndex = -1;
foreach (I3ReportCol col in rd.Cols)
{
colIndex++;
if (col.Width == 0)
{
sheet.SetColumnHidden(colIndex, true);
}
else
{
//int width = (int)Math.Ceiling(col.Width * 34.612159); //向上取整，与excel中保持一致 //34.612159是从ebiao导入excel处获取的
//在列旁边按住鼠标不动，可以显示默认单位与像素的对应关系，10单位对应85像素，即1像素对应10/85单位 (好像与excel默认字体有关)
//SetColumnWidth的单位是1/256，因此1像素对应10*256/85=30.117647
double dw = (double)col.Width * (double)10 * (double)256 / (double)85;
int width = (int)Math.Ceiling(dw);
sheet.SetColumnWidth(colIndex, width);
}
}
//导出数据
int rowCount = 0;
foreach (I3PrintArea area in rd.PrintAreas.Dic.Values)
{
exportAreaToSheet(area, workbook, sheet, ref rowCount, styleDic, fontDic);
}
//页面设置 //列宽会有微调导致在excel中左右分页
sheet.SetMargin(MarginType.RightMargin, rd.PageSetting.PaperRightMarginMM / (float)25.4);
sheet.SetMargin(MarginType.TopMargin, rd.PageSetting.PaperTopMarginMM / (float)25.4);
sheet.SetMargin(MarginType.LeftMargin, rd.PageSetting.PaperLeftMarginMM / (float)25.4);
sheet.SetMargin(MarginType.BottomMargin, rd.PageSetting.PaperBottomMarginMM / (float)25.4);
sheet.PrintSetup.Landscape = rd.PageSetting.PaperOrientation == PaperOrientation.横向;
int paperType = rd.PageSetting.GetNPOIPaperType();
if (paperType == 0) //自定义，不清楚NPOI中怎样设置，用A4
{
sheet.PrintSetup.PaperSize = 9;
}
else if (paperType == -1) //未打到对应关系
{
sheet.PrintSetup.PaperSize = 9;
}
else
{
sheet.PrintSetup.PaperSize = (short)paperType;
}
sheet.IsPrintGridlines = true;
sheet.FitToPage = false;//默认值是true
//设置锁定
sheet.ProtectSheet("{D49C4D85-F46E-456F-9C71-DB7D880B5B04}");
sheet.IsPrintGridlines = false; //不设置这个，打印时会打印出空白单元格的网络线（即使没有网格线也会打印）
sheetIndex++;
}
string tmpFileName = getTmpFileName();
using (FileStream file = new FileStream(tmpFileName, FileMode.Create))
{
workbook.Write(file);
file.Close();
}
I3PCUtil.CreateAndWaitProcessByEvent(null, tmpFileName, "", IntPtr.Zero, 0, 0);
}
protected void btnImport_Click(object sender, EventArgs e)
{
string strPrintId = this.strPrintId.Value;
TMisMonitorSubtaskVo objSubTask = new TMisMonitorSubtaskVo();
objSubTask.TASK_ID = strPrintId;
DataTable dtSub = new TMisMonitorSubtaskLogic().SelectByTable(objSubTask);
string strSampleIDs = "";
for (int i = 0; i < dtSub.Rows.Count; i++)
{
GetPoint_UnderTask(dtSub.Rows[i]["ID"].ToString(), ref strSampleIDs);
}
//获取基本信息
DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleIDs, "021", "0");
int iPageCount = dt.Rows.Count / 17;
if (dt.Rows.Count % 17 != 0)
{
iPageCount += 1;
}
FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/QHDSamplingCode.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//sheet复制
for (int k = 1; k < iPageCount; k++)
{
hssfworkbook.CloneSheet(0);
hssfworkbook.SetSheetName(k, "Sheet" + (k + 1).ToString());
}
for (int m = 1; m <= iPageCount; m++)
{
ISheet sheet = hssfworkbook.GetSheet("Sheet" + m.ToString());
sheet.GetRow(23).GetCell(0).SetCellValue("样品交接数量： " + dt.Rows.Count.ToString());
sheet.GetRow(2).GetCell(0).SetCellValue(string.Format(" 采 样 日 期： {0} 年 {1} 月 {2} 日", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString()));
sheet.GetRow(3).GetCell(0).SetCellValue(string.Format(" 样品交接日期： {0} 年 {1} 月 {2} 日 {3} 点 {4} 分", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Hour.ToString(), DateTime.Now.Minute.ToString()));
sheet.GetRow(1).GetCell(6).SetCellValue(string.Format("第 {0} 页 共 {1} 页", m.ToString(), iPageCount.ToString()));
DataTable dtNew = new DataTable();
dtNew = dt.Copy();
dtNew.Clear();
for (int n = (m - 1) * 17; n < m * 17; n++)
{
if (n >= dt.Rows.Count)
{
break;
}
dtNew.ImportRow(dt.Rows[n]);
}
for (int i = 0; i < dtNew.Rows.Count; i++)
{
string strItmeNum = "";
TMisMonitorResultVo objResult = new TMisMonitorResultVo();
objResult.SAMPLE_ID = dtNew.Rows[i]["ID"].ToString();
DataTable dtResult = new TMisMonitorResultLogic().SelectByTable(objResult);
for (int j = 0; j < dtResult.Rows.Count; j++)
{
TBaseItemInfoVo objItem = new TBaseItemInfoVo();
objItem.ID = dtResult.Rows[j]["ITEM_ID"].ToString();
objItem.IS_SAMPLEDEPT = "否";
DataTable dtItem = new TBaseItemInfoLogic().SelectByTable(objItem);
if (dtItem.Rows.Count > 0 && dtItem.Rows[0]["ITEM_NUM"].ToString().Length > 0)
{
strItmeNum += (strItmeNum.Length > 0) ? "," + dtItem.Rows[0]["ITEM_NUM"].ToString() : dtItem.Rows[0]["ITEM_NUM"].ToString();
}
}
sheet.GetRow(i + 6).GetCell(0).SetCellValue((i + 1).ToString());
sheet.GetRow(i + 6).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_CODE"].ToString());
sheet.GetRow(i + 6).GetCell(6).SetCellValue(strItmeNum);
}
}
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("水质样品交接记录表.xls", Encoding.UTF8));
curContext.Response.BinaryWrite(stream.GetBuffer());
curContext.Response.End();
}
}
