/// <summary>
/// Perform the column sizing for the sheet, and then clear out the column widths
/// </summary>
private void PerformColumnResize()
{
if (_configuration.AutoSizeColumns && _colWidths != null)
{
for (var i = 0; i < _colWidths.Length; i++)
{
_sheet.Columns[i].Width = C1XLBook.PixelsToTwips(_colWidths[i]);
}
}
_colWidths = null;
}
private void AutoSizeColumns(XLSheet sheet)
{
for (int c = 0; c < sheet.Columns.Count; c++)
{
int colWidth = -1;
for (int r = 0; r < sheet.Rows.Count; r++)
{
object value = sheet[r, c].Value;
if (value != null)
{
// get value (unformatted at this point)
string text = value.ToString();
// format value if cell has a style with format set
var s = sheet[r, c].Style;
if (s != null && s.Format.Length > 0 && value is IFormattable)
{
string fmt = XLStyle.FormatXLToDotNet(s.Format);
text = ((IFormattable)value).ToString(fmt, CultureInfo.CurrentCulture);
}
// get font (default or style)
var font = this._book.DefaultFont;
if (s != null && s.Font != null)
{
font = s.Font;
}
// measure string (add a little tolerance)
_tblMeasure.FontFamily = new FontFamily(font.FontName);
_tblMeasure.FontSize = 3 * font.FontSize / 2;
_tblMeasure.FontWeight = font.Bold ? FontWeights.Bold : FontWeights.Normal;
_tblMeasure.FontStyle = font.Italic ? FontStyles.Italic : FontStyles.Normal;
_tblMeasure.Text = text;
// keep widest so far
int w = (int)(_tblMeasure.ActualWidth);
if (w > colWidth)
{
colWidth = w;
}
}
}
// done measuring, set column width
if (colWidth > -1)
{
sheet.Columns[c].Width = C1XLBook.PixelsToTwips(colWidth);
}
}
}
/// <summary>
/// 自动设置列宽
/// </summary>
/// <param name="sheet"></param>
private void AutoSizeColumns(XLSheet sheet)
{
using (Graphics g = Graphics.FromHwnd(IntPtr.Zero))
{
for (int c = 0; c < sheet.Columns.Count; c++)
{
int colWidth = -1;
for (int r = 0; r < sheet.Rows.Count; r++)
{
object value = sheet[r, c].Value;
if (value != null)
{
// get value (unformatted at this point)
string text = value.ToString();
// format value if cell has a style with format set
C1.C1Excel.XLStyle s = sheet[r, c].Style;
if (s != null && s.Format.Length > 0 && value is IFormattable)
{
string fmt = XLStyle.FormatXLToDotNet(s.Format);
text = ((IFormattable)value).ToString(fmt, CultureInfo.CurrentCulture);
}
// get font (default or style)
Font font = this.c1XLBook1.DefaultFont;
if (s != null && s.Font != null)
{
font = s.Font;
}
// measure string (add a little tolerance)
Size sz = Size.Ceiling(g.MeasureString(text + "XX", font));
// keep widest so far
if (sz.Width > colWidth)
{
colWidth = sz.Width;
}
}
}
// done measuring, set column width
if (colWidth > -1)
{
sheet.Columns[c].Width = C1XLBook.PixelsToTwips(colWidth);
}
}
}
}
private void AutoSizeColumns(XLSheet sheet)
{
using (Graphics graphics = Graphics.FromHwnd(IntPtr.Zero))
{
for (int index1 = 0; index1 < sheet.Columns.Count; ++index1)
{
int num = -1;
for (int index2 = 0; index2 < sheet.Rows.Count; ++index2)
{
object obj = sheet[index2, index1].Value;
if (obj != null)
{
string str = obj.ToString();
XLStyle style = sheet[index2, index1].Style;
if (style != null && style.Format.Length > 0 && obj is IFormattable)
{
string dotNet = XLStyle.FormatXLToDotNet(style.Format);
str = ((IFormattable)obj).ToString(dotNet, (IFormatProvider)CultureInfo.CurrentCulture);
}
Font font = this.oBook.DefaultFont;
if (style != null && style.Font != null)
{
font = style.Font;
}
Size size = Size.Ceiling(graphics.MeasureString(str + "XX", font));
if (size.Width > num)
{
num = size.Width;
}
}
}
if (num > -1)
{
sheet.Columns[index1].Width = C1XLBook.PixelsToTwips((double)num);
}
}
}
}
//===========================================================================================
#region ** Save a C1FlexGrid into an XLSheet
private void SaveSheet(C1FlexGrid flex, XLSheet sheet, bool fixedCells)
{
// account for fixed cells
int frows = flex.Rows.Fixed;
int fcols = flex.Cols.Fixed;
if (fixedCells)
{
frows = fcols = 0;
}
// copy dimensions
int lastRow = flex.Rows.Count - frows - 1;
int lastCol = flex.Cols.Count - fcols - 1;
if (lastRow < 0 || lastCol < 0)
{
return;
}
XLCell cell = sheet[lastRow, lastCol];
// set default properties
sheet.Book.DefaultFont = flex.Font;
sheet.DefaultRowHeight = C1XLBook.PixelsToTwips(flex.Rows.DefaultSize);
sheet.DefaultColumnWidth = C1XLBook.PixelsToTwips(flex.Cols.DefaultSize);
// prepare to convert styles
_styles = new Hashtable();
// set row/column properties
for (int r = frows; r < flex.Rows.Count; r++)
{
// size/visibility
Row fr = flex.Rows[r];
XLRow xr = sheet.Rows[r - frows];
if (fr.Height >= 0)
{
xr.Height = C1XLBook.PixelsToTwips(fr.Height);
}
xr.Visible = fr.Visible;
// style
XLStyle xs = StyleFromFlex(fr.Style);
if (xs != null)
{
xr.Style = xs;
}
}
for (int c = fcols; c < flex.Cols.Count; c++)
{
// size/visibility
Column fc = flex.Cols[c];
XLColumn xc = sheet.Columns[c - fcols];
if (fc.Width >= 0)
{
xc.Width = C1XLBook.PixelsToTwips(fc.Width);
}
xc.Visible = fc.Visible;
// style
XLStyle xs = StyleFromFlex(fc.Style);
if (xs != null)
{
xc.Style = xs;
}
}
// load cells
for (int r = frows; r < flex.Rows.Count; r++)
{
for (int c = fcols; c < flex.Cols.Count; c++)
{
// get cell
cell = sheet[r - frows, c - fcols];
// apply content
cell.Value = flex[r, c];
// apply style
XLStyle xs = StyleFromFlex(flex.GetCellStyle(r, c));
if (xs != null)
{
cell.Style = xs;
}
}
}
}
private async void ExcelButton_Click(object sender, RoutedEventArgs e)
{
// 現在、FlexGrid に表示されている順のデータ
var currentData = this.flexgrid1.Rows.Select(r => r.DataItem).Cast <Book>();
// Excel データの作成
// https://docs.grapecity.com/help/c1/uwp/uwp_excel/#Step_2_of_4-_Adding_Content_to_a_C1XLBook.html
// 新しい Excel ワークブックを作成
var xlBook = new C1XLBook();
// デフォルトで作成されたシートを取得
XLSheet sheet = xlBook.Sheets[0];
// シートの中身を書き込みます
int rowIndex = 0;
// ヘッダー行
sheet[rowIndex, 0].Value = "書名";
sheet[rowIndex, 1].Value = "ISBN";
sheet[rowIndex, 2].Value = "バーコード";
sheet.Columns[2].Width
= C1XLBook.PixelsToTwips(this.HiddenBarCode.ActualWidth);
sheet[rowIndex, 3].Value = "価格";
// データ行
foreach (var book in currentData)
{
rowIndex++;
// バーコードの画像を作る
this.HiddenBarCode.Text = book.IsbnWithoutCheckDigit;
C1Bitmap bitmap = new C1Bitmap();
using (var ms = new InMemoryRandomAccessStream().AsStream())
{
await this.HiddenBarCode.SaveAsync(ms, ImageFormat.Png);
bitmap.Load(ms);
}
// 行の高さをバーコードの画像に合わせる
sheet.Rows[rowIndex].Height
= C1XLBook.PixelsToTwips(this.HiddenBarCode.ActualHeight);
// 1行分のデータとバーコード画像をセット
sheet[rowIndex, 0].Value = book.Title;
sheet[rowIndex, 1].Value = book.Isbn;
sheet[rowIndex, 2].Value = bitmap;
sheet[rowIndex, 3].Value = book.Price;
}
// Excel ファイルへの書き出し
// https://docs.grapecity.com/help/c1/uwp/uwp_excel/#Step_3_of_4-_Saving_the_XLSX_File.html
var picker = new FileSavePicker()
{
SuggestedStartLocation = PickerLocationId.DocumentsLibrary
};
picker.FileTypeChoices.Add("Open XML Excel ファイル", new string[] { ".xlsx", });
picker.FileTypeChoices.Add("BIFF Excel ファイル", new string[] { ".xls", });
picker.SuggestedFileName = "BarCodeControlSample";
var file = await picker.PickSaveFileAsync();
if (file != null)
{
var fileFormat = Path.GetExtension(file.Path).Equals(".xls") ? FileFormat.OpenXmlTemplate : FileFormat.OpenXml;
await xlBook.SaveAsync(file, fileFormat);
}
}
