private void exportXLSX_Click(object sender, RoutedEventArgs e)
{
var newFile = @"newbook.core.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
// XSSFWorkbook : *.xlsx >= Excel2007
// HSSFWorkbook : *.xls < Excel2007
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet Name");
// 所有索引都从0开始
// 合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex); //创建行
row.Height = 30 * 80;
row.CreateCell(0).SetCellValue("this is content");
sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
rowIndex++;
// 插入图片
byte[] data = File.ReadAllBytes(@"image.jpg");
int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFDrawing drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = helper.CreateClientAnchor() as XSSFClientAnchor;
anchor.Col1 = 10;
anchor.Row1 = 0;
XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict.Resize();
// 新建sheet
var sheet2 = workbook.CreateSheet("Sheet2");
// 更改样式
var style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
var style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = HSSFColor.Yellow.Index2;
style2.FillPattern = FillPattern.SolidForeground;
var cell2 = sheet2.CreateRow(0).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(0);
cell2 = sheet2.CreateRow(1).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(1);
//保存
workbook.Write(fs);
}
txtStatus.Text = "writing xlsx successful!";
}
private static void CreateChartDataSheet(Stream stream, IWorkbook workbook)
{
var sheet = workbook.CreateSheet("Chart");
var drawing = sheet.CreateDrawingPatriarch();
var data = new byte[stream.Length];
stream.Read(data, 0, (int)stream.Length);
var picInd = workbook.AddPicture(data, PictureType.PNG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFClientAnchor anchor = helper?.CreateClientAnchor() as XSSFClientAnchor;
if (anchor != null)
{
anchor.Col1 = 0;
anchor.Row1 = 0;
var pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict?.Resize();
}
}
public void TestInvalidURLs()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] invalidURLs =
{
//"http:\\apache.org",
"www.apache .org",
"c:\\temp",
"\\poi"
};
foreach (String s in invalidURLs)
{
try
{
CreateHelper.CreateHyperlink(HyperlinkType.Url).Address = (s);
Assert.Fail("expected ArgumentException: " + s);
}
catch (ArgumentException)
{
}
}
}
public SXSSFCreationHelper(SXSSFWorkbook workbook)
{
this.helper = new XSSFCreationHelper(workbook.XssfWorkbook);
this.wb = workbook;
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls =
{
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address = (s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink = (link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
}
public void TestCommentShowsBox()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet();
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFCell cell = (XSSFCell)sheet.CreateRow(0).CreateCell(0);
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFCreationHelper factory = (XSSFCreationHelper)wb.GetCreationHelper();
XSSFClientAnchor anchor = (XSSFClientAnchor)factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 3;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + 5;
XSSFComment comment = (XSSFComment)drawing.CreateCellComment(anchor);
XSSFRichTextString str = (XSSFRichTextString)factory.CreateRichTextString("this is a comment");
comment.String = str;
cell.CellComment = comment;
XSSFVMLDrawing vml = sheet.GetVMLDrawing(false);
CT_Shapetype shapetype = null;
ArrayList items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
using (MemoryStream ws = new MemoryStream())
{
wb.Write(ws);
using (MemoryStream rs = new MemoryStream(ws.GetBuffer()))
{
wb = new XSSFWorkbook(rs);
sheet = (XSSFSheet)wb.GetSheetAt(0);
vml = sheet.GetVMLDrawing(false);
shapetype = null;
items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
//wb.Write(new FileStream("comments.xlsx", FileMode.Create));
//using (MemoryStream ws2 = new MemoryStream())
//{
// vml.Write(ws2);
// throw new System.Exception(System.Text.Encoding.UTF8.GetString(ws2.GetBuffer()));
//}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
}
}
}
public void computeResult(string uniqueID, IDictionary <int, string> eventDict, XSSFWorkbook wb, string sheetName, List <string> responseList,
List <string> questionTypeList, IDictionary <string, string> uniqueAnswerDict,
List <string> questionList, List <List <string> > answerList, string resultType = "Normal")
{
//Create bold font for excel
XSSFCellStyle xStyle = boldFont(wb);
int rowCount = 0;
int totalNumChoiceLength = 0;
if (resultType != "Percent")
{
XSSFCreationHelper createHelper = (XSSFCreationHelper)wb.GetCreationHelper();
}
XSSFSheet summarySheet = (XSSFSheet)wb.CreateSheet(sheetName);
//Group all the different answers list together
foreach (KeyValuePair <string, string> entry in uniqueAnswerDict)
{
// Create a row and put some cells in it. Rows are 0 based.
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(entry.Value));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
int cellCount = 1;
List <string> answerChoiceList = JsonConvert.DeserializeObject <List <string> >(entry.Key);
if (answerChoiceList.Count > totalNumChoiceLength)
{
totalNumChoiceLength = answerChoiceList.Count;
}
foreach (string choice in answerChoiceList)
{
row.CreateCell(cellCount).SetCellValue(choice);
row.GetCell(cellCount).CellStyle = xStyle;
cellCount += 1;
}
rowCount += 1;
}
xStyle = boldFont(wb);
//xStyle.FillPattern = FillPattern.NoFill;
List <string> openEndedList = new List <string>();
Dictionary <int, List <string> > openEndedResponseDict = new Dictionary <int, List <string> >();
Dictionary <string, int> choiceResponseDict = new Dictionary <string, int>();
//Get a list of response
foreach (var response in responseList)
{
FeedbackEntity feedbackEntity = JsonConvert.DeserializeObject <FeedbackEntity>(response);
List <string> feedbackResponse = JsonConvert.DeserializeObject <List <string> >(feedbackEntity.Response);
int qnNo = 1;
foreach (string feedback in feedbackResponse)
{
List <string> openEndedResponse = new List <string>();
if (questionTypeList[qnNo - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
if (openEndedResponseDict.ContainsKey(qnNo))
{
openEndedResponseDict[qnNo].Add(feedback);
}
else
{
openEndedResponse.Add(feedback);
openEndedResponseDict.Add(qnNo, openEndedResponse);
}
}
else if (questionTypeList[qnNo - 1] == "2") //Multiple Choice
{
if (choiceResponseDict.ContainsKey(qnNo + "||" + feedback))
{
choiceResponseDict[qnNo + "||" + feedback] += 1;
}
else
{
choiceResponseDict.Add(qnNo + "||" + feedback, 1);
}
}
qnNo += 1;
}
}
//Print out all the question number and text
int qnCount = 1;
foreach (var qnText in questionList)
{
if (questionTypeList[qnCount - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
openEndedList.Add("Q" + qnCount + "." + qnText);
}
else if (questionTypeList[qnCount - 1] == "2") //Multiple Choice
{
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue("Q" + qnCount + ". ");
row.GetCell(0).CellStyle = xStyle;
row.CreateCell(0 + totalNumChoiceLength + 1).SetCellValue(qnText);
xStyle = normalFont(wb);
color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
for (int i = 0; i < answerList[qnCount - 1].Count; i++)
{
//Print out all the question response
if (choiceResponseDict.ContainsKey(qnCount + "||" + answerList[qnCount - 1][i]))
{
double displayValue = 0;
if (resultType == "Percent")
{
double value = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
displayValue = value / responseList.Count * 100;
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2) + "%");
}
else
{
displayValue = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2));
}
row.GetCell(1 + i).CellStyle = xStyle;
}
else
{
row.CreateCell(1 + i).SetCellValue(0);
row.GetCell(1 + i).CellStyle = xStyle;
}
xStyle.SetFillForegroundColor(color); //to end off the color xStyle
}
rowCount += 1;
}
qnCount++;
}
summarySheet.CreateRow(rowCount);
rowCount++;
xStyle = boldFont(wb);
//Print out all the openEnded Questions
foreach (var openEnded in openEndedList)
{
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue(openEnded);
row.GetCell(0).CellStyle = xStyle;
rowCount += 1;
int qnNo = Convert.ToInt32(openEnded.Split('.')[0].Remove(0, 1));
//Create rows for response answers
if (openEndedResponseDict.ContainsKey(qnNo))
{
foreach (var response in openEndedResponseDict[qnNo])
{
xStyle = normalFont(wb);
row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(1).SetCellValue(response);
row.GetCell(1).CellStyle = xStyle;
rowCount += 1;
}
}
else
{
rowCount += 1;
}
}
for (int i = 1; i <= 15; i++) // this will aply it form col 1 to 10
{
summarySheet.AutoSizeColumn(i);
}
}
